Rank query help

[weazer via AccessMonster.com]

Below is the SQL for a query that I have. Below the SQL is the results. What
I need to do is rank the score by each name in descending order. When the
name changes I need the rank to start at 1 again.

Thanks in advance for your help. I am stuck.


SELECT [Plant].Name, [Plant].DefectCode, [Plant].Score
FROM [Plant]
GROUP BY [Plant].Name, [Plant].DefectCode, [Plant].Score
ORDER BY [Plant].Name, [Plant].Score DESC;



Name DefectCode Score
Plant A 2000 Widget 100 8
Plant A 2000 Widget 105 5
Plant A 2000 Widget 106 4
Plant A 2001 Widget 105 4
Plant A 2001 Widget 102 3
Plant A 2001 widget 157 2
Plant B 2000 Widget 105 10
Plant B 2000 widget 100 8
Plant B 2000 widget 120 3
Plant B 2001 widget 100 11
Plant B 2001 widget 110 10
Plant B 2001 widget 107 7
Plant C 1999 widget 100 8
Plant C 1999 widget 110 5
Plant C 1999 widget 120 4
Plant C 2004 widget 111 5
Plant C 2004 widget 111 5
Plant C 2004 widget 111 5

 

[Guest]

Try this --
SELECT Q.Name, Q.DefectCode, Q.Score, (SELECT COUNT(*) FROM Plant Q1
WHERE Q1.[Name] = Q.[Name]
AND Q1.Score > Q.Score)+1 AS Rank
FROM Plant AS Q
ORDER BY Q.Name, Q.Score DESC;

 

[weazer via AccessMonster.com]

Thanks for the reply. When I ran this SQL it gave me a syntax error with the
following:

SELECT Q.Name, Q.DefectCode, Q.Score, (SELECT COUNT(*) FROM Plant Q1
WHERE Q1.[Name] = Q.[Name]
AND Q1.Score > Q.Score)+1

Everything I tried did not correct the error.

Please le me know if you see where the syntax error is.

Thanks again.

Try this --
SELECT Q.Name, Q.DefectCode, Q.Score, (SELECT COUNT(*) FROM Plant Q1
WHERE Q1.[Name] = Q.[Name]
AND Q1.Score > Q.Score)+1 AS Rank
FROM Plant AS Q
ORDER BY Q.Name, Q.Score DESC;

Below is the SQL for a query that I have. Below the SQL is the results. What
I need to do is rank the score by each name in descending order. When the

[quoted text clipped - 26 lines]

Plant C 2004 widget 111 5
Plant C 2004 widget 111 5

 

[Smartin]

Thanks for the reply. When I ran this SQL it gave me a syntax error with the
following:

SELECT Q.Name, Q.DefectCode, Q.Score, (SELECT COUNT(*) FROM Plant Q1
WHERE Q1.[Name] = Q.[Name]
AND Q1.Score > Q.Score)+1

Everything I tried did not correct the error.

Please le me know if you see where the syntax error is.

Thanks again.

Try this --
SELECT Q.Name, Q.DefectCode, Q.Score, (SELECT COUNT(*) FROM Plant Q1
WHERE Q1.[Name] = Q.[Name]
AND Q1.Score > Q.Score)+1 AS Rank
FROM Plant AS Q
ORDER BY Q.Name, Q.Score DESC;

Below is the SQL for a query that I have. Below the SQL is the results. What
I need to do is rank the score by each name in descending order. When the

[quoted text clipped - 26 lines]

Plant C 2004 widget 111 5
Plant C 2004 widget 111 5

Prolly needs a closing paren before "AS Rank":

SELECT Q.Name, Q.DefectCode, Q.Score, (SELECT COUNT(*) FROM Plant Q1
WHERE Q1.[Name] = Q.[Name]
AND Q1.Score > Q.Score)+1 ) AS Rank
FROM Plant AS Q
ORDER BY Q.Name, Q.Score DESC;