W
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
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