Ranking the records.

[rahmad]

Last time I'm trying this

SELECT Test.Customer, Test.Year, Test.Score, (SELECT Count(Test.Score) FROM
Test As X WHERE X.Year = Test.Year And X.Score <= Test.Score) AS Rank
FROM Test
GROUP BY Test.Customer, Test.Year, Test.Score
ORDER BY Test.Score DESC;

the query return me

Customer Year Score Ranking Desired Ranking
A 1999 8 5 1
B 1999 6 4 2
C 1999 5 3 3
D 1999 5 3 3
E 1999 4 1 4

I want the biggest score number at the top rank.
Which word should be revised?
Can I sort the rank Ascending?
or how to return top 10 values of the score which is sorted
descendly.

 

[John Spencer]

SELECT Test.Customer, Test.Year, Test.Score
, (SELECT Count(Test.Score)
FROM Test As X
WHERE X.Year = Test.Year
And X.Score > Test.Score) + 1 AS Rank
FROM Test
GROUP BY Test.Customer, Test.Year, Test.Score
ORDER BY Test.Score DESC;

That will come close to what you want. The rank values would be
1, 2, 3, 3, and 5 for the scores 8, 6, 5, 5, and 4.

I know of no easy way to get 1, 2, 3, 3, and 4 for the ranking.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Chris2]

Last time I'm trying this

SELECT Test.Customer, Test.Year, Test.Score, (SELECT Count(Test.Score) FROM
Test As X WHERE X.Year = Test.Year And X.Score <= Test.Score) AS Rank
FROM Test
GROUP BY Test.Customer, Test.Year, Test.Score
ORDER BY Test.Score DESC;

the query return me

Customer Year Score Ranking Desired Ranking
A 1999 8 5 1
B 1999 6 4 2
C 1999 5 3 3
D 1999 5 3 3
E 1999 4 1 4

I want the biggest score number at the top rank.
Which word should be revised?
Can I sort the rank Ascending?
or how to return top 10 values of the score which is sorted
descendly.

rahmad,

Table:

Create a blank MS Access database. You can copy and paste this DDL SQL query into an MS
Access Query, executing it in order to create the table.


CREATE TABLE Test
(Customer TEXT(1)
,[Year] DATETIME
,Score INTEGER
,CONSTRAINT pk_Test
PRIMARY KEY (Customer
,[Year]
,Score)
)


Sample Data:

As listed above, except I used 01/01/1999 for the DATETIME values in the [Year] column


Query:

SELECT T1.Customer
,T1.[Year]
,T1.Score
,(SELECT Count(T2.Score)
FROM (SELECT DISTINCT
T3a.Score
,T3a.[Year]
FROM Test As T3a) AS T2
WHERE T2.[Year] = T1.[Year]
AND T2.Score >
(SELECT MIN(T3b.Score)
FROM Test T3b
WHERE T3b.[Year] = T1.[Year]
AND T3b.Score = T1.Score)) + 1
AS Rank
FROM Test AS T1
GROUP BY T1.Customer
,T1.[Year]
,T1.Score
ORDER BY T1.Score DESC;


Try this out. It appears to work on your test data.


Results:

Customer, Year, Score, Rank
A, 01/01/1999, 8, 1
B, 01/01/1999, 6, 2
C, 01/01/1999, 5, 3
D, 01/01/1999, 5, 3
E, 01/01/1999, 4, 4



Sincerely,

Chris O.