ranking in a query

[ashni]

heyy
i have a query which totals for me points for a game.i want the query to also rank for me the record.
i have player id and total points in the query. i would like a have a new column which would rank the players.
please let me know how i can do this.
thanks
ash

 

[dcb]

You want to post some sample data and an example of what you looking for?
Have a look at the IIF function

 

[wilpeter]

Let's say your query that totals points is called QryTotalPoints. It is Grouped by IDNo and has a field called Points. Create a new query with the SQL code:
SELECT ((SELECT COUNT(*) FROM QryTotalPoints AS Tmp
WHERE Tmp.Points > QryTotalPoints.Points) +1) AS Num, IDNo, Points
FROM QryTotalPoints
ORDER BY QryTotalPoints.Points DESC;

The Num field is the sequential number from 1.

 

[wilpeter]

Forgot to mention that if two "SumOfPoints" are identical, they will share the sequential position (e.g. 2 if both second place) and next number will be 4.
Likely your field name will be "SumOfPoints" instead of Points.

 

[ashni]

hey thanx...buh it not helping..is it possible i give u my field names n then u give me the SQL code accordingly?
its Player ID, Total_Points and the new column i want is rank.
thank you

 

[ashni]

and forgot to mention my query is called Player Scores Query Under10 Female

 

[wilpeter]

First, since this is a learning experience, you should avoid spaces in your query names, field names, table names, etc. as that forces using square brackets around them in codes.
SQL:
SELECT ((SELECT COUNT(*) FROM [Player Scores Query Under10 Female] AS Tmp WHERE Tmp.SumOfPoints > [Player Scores Query Under10 Female].SumOfPoints)+1) AS Rank, [Player ID], SumOfPoints
FROM [Player Scores Query Under10 Female]
ORDER BY [Player Scores Query Under10 Female].SumOfPoints DESC;

 

[ashni]

hey thanx alot..it worked...thank you