Getting the rank (ORDER BY position) of a record in a table

Question

Getting the rank (ORDER BY position) of a record in a table

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

The problem is not how to get the rank, but what is the best method to get it. Remember that he must be able to select a single line and not the entire table and still get the good ranking of the person.

Michael Hirst SSC Veteran Points: 205 More actions · Answer 2

That query with the where clause will return one line. However, also note that he requests

People should be able to submit their times and they will show up in a listing along with their overall ranking. The problem is they should be able to sort the listing on different criteria and the ranking remain the same.

That implies multiple records.

James Spibey SSC Enthusiast Points: 128 More actions · Answer 3

That actually seems to work but with one minor flaw. With my test data

id time name

1 01:15 Bob

2 01:28 Bill

3 01:57 Terry

4 01:15 John

I get a ranking of 2, 2, 3 , 4 rather than 1, 1, 2, 3

If I change the <= to <, I get 0,0,2,3. Is there a way to fix this?

Michael Hirst SSC Veteran Points: 205 More actions · Answer 4

Try

select *, (select count(*) + 1 from tbl t where t.time < m.time) as Rank from tbl m where name = 'xxxx'

or

select m.name, count(*) + 1 as Rank from tbl m join tbl t on t.time < m.time where m.name = 'xxxx' group by m.name

This should give you 1, 1, 3, 4 - which I think is how it is usually shown.