February 8, 2005 at 11:53 am
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.
February 8, 2005 at 12:00 pm
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.
February 9, 2005 at 5:36 am
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?
February 9, 2005 at 12:48 pm
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.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply