SQL Query to sort the ranklist....

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL Query to sort the ranklist....

Author

Topic

great_mamun
Starting Member

14 Posts

Posted - 2011-03-22 : 03:57:39

Dear All,
I have a table named result (user_id, total_score, exam_duration).

Sample Data:
user_id total_score exam_duration
-------------------------------------
id-1 7 00:05:00
id-2 10 00:02:00
id-3 10 00:10:00
id-4 7 00:12:00
id-5 7 00:05:00
id-6 10 00:10:00
id-7 6 00:07:45
id-8 9 00:09:45
id-9 6 00:13:23
id-10 4 00:12:20

Expected_Ouput:
Rank user_id total_score exam_duration
---------------------------------------------
1 id-2 10 00:02:00 -> eql score but less time
2 id-3 10 00:10:00 -> both eql
2 id-6 10 00:10:00 -> both eql so same rank

3 id-8 9 00:09:45

4 id-1 7 00:05:00 -> both eql
4 id-5 7 00:05:00 -> both eql so same rank
5 id-4 7 00:12:00 -> eql score but more time

6 id-7 6 00:07:45 -> eql score but less time
7 id-9 6 00:13:23 -> eql score but more time

8 id-10 4 00:12:20 -> less score

What I want, order by total_score desc, if equal then sort by exam_duration asc , if both equal then same rank....

I would highly appreciate if you would plz give the sql query (only sql query)

Best Regards,
Abdullah Al Mamun

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-03-22 : 04:04:50

Can you show us what you have tried ?

Hint for you to start with:
Use Dense Rank function
order by total_score desc and exam_duration asc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources