ranking results query

[dastr]

Hi all,
I have the following issue that is causing me a headache for more than a week now. I need to rank the below entries:
I have Table A incl the first two colums below(Name; Score) - I need to get column 3 in my result:
Name Score Best
Inna 2 1st best
Inna 4 2nd best
Luca 7 1st best
The idea is to compare the score for each name and the lowest score gets the 1st best, 2nd lowest – 2nd best and like that till 4th best. I thought using the below, but get a different outcome that the desired?

SELECT (SELECT COUNT(*) FROM [Table A] WHERE Score >= s.Score) AS Rank, s.Name, s.Score,
FROM [Table A] AS s
ORDER BY s.Score;

Any thought will be more than welcome.
Thx all

 

[pbaldy]

You would also need the name in the criteria.

 

[dastr]

Hi,
Correct, I have overlooked it, but still cant get the desired ranking.
Any ideas how I may do this?
Thanks

 

[pbaldy]

What is your SQL now? What is the result vs your desired result?

 

[sparks80]

As Paul rightly says you need to include the name of the person in the subquery like this. I have limited the query to only displaying the top 4 scores by specifying that the calculated rank < 5.

SELECT s.Person, s.Score, (SELECT COUNT(*) FROM [tblScores] WHERE (((Score) <= s.Score) AND ([Person]=s.Person))) AS Rank
FROM tblScores AS s
WHERE ((((SELECT COUNT(*) FROM [tblScores] WHERE (((Score) <= s.Score) AND ([Person]=s.Person))))<5))
ORDER BY s.Person, s.Score;


It is worth checking out Allen Browne's wise words of advice here: http://allenbrowne.com/subquery-01.html#TopN

 

[dastr]

Hi Sparks,
When I follow the above and set rank <5, I get also Rank 0 (besides rank 1,2,3 and 4).
For example, for one name I would have Rank 0 instead of Rank 1 - if I manually do the ranking.
Do you have any idea on what causes the above?
Thank you.