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

Question

Post reply

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

James Spibey

SSC Enthusiast

Points: 128
More actions
February 7, 2005 at 8:54 am

#164463

Hi,
I have an interesting problem. I have a table which contains a series of names and times
entry_id name time
1 john 1:32
2 bill 1:54
3 sarah 1:12
What I need to be able to do is for each of those records to be able to display their 'ranking' based on the time column.
What's the best way to do this? It needs to be scalable to many thousands of records so performance is definately a priority.
Thanks

Viewing 15 posts - 1 through 15 (of 18 total)

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

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

Couple of alternatives in this thread:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=159332#bm159359

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

Thanks, that was useful. However, I think I haven't explained my problem properley

I need to be able to select inidividual records from the table and still show the rank. The only way I could think of to do this was to add a 'rank' column to the table and have it updated via a scheduled task or whatever so that as new records are added, the rank is recalculated. This is obviously going to be a nightmare as the table size grows so I was hoping there was another method.

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

Yes it will be a nightware. You'll have to add a column Ranking in your Table, populate it using the methods shown in the links, and then on every insert/update/delete you'll have to update that column. I hope that this table doesn't have a lot of transaction and a lot of rows because those type of queries are not the fastest to run.. even when properly indexed.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Maybe it's just me, but can you give some more details what will this be used for?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

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

Well, it's going to be a ranking table for lap times for a computer game. 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.

So, am I out of luck?

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

Hm, in this case I'll second Remi.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Don Caldwell Valued Member Points: 64 More actions · Answer 7

A quick idea based on all the time values being [minutes:seconds]. Store the time as a whole number. 1:35 becomes 135. Now you order (rank) using it.

For display convert to string, pick two right chars (seconds), insert a colon then use remaining char(s) (minutes).

Don

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

Thanks for the reply don but I don't see how that is any different than ordering by a normal date column? How would I be able to determine the rank of a record when it is selected individually?

Don Caldwell Valued Member Points: 64 More actions · Answer 9

Sorry I didn't take enough time to understand your question.

One more shot at it. How about two step query?

Insert in temp table all your records using "select rank=count(*), entry_id, name, time from your table order by time. Then select specific record by your criteria from temp table which includes the ranking at the time this query ran.

Don

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

That's more like it That sounds like it will work, the only thing I'm concerned about is performance because we could be talking about many thousands of rows. What I may suggest is that we only generate the rankings every x hours which would reduce the load on the servers.

Thanks again.

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

You'll have to do load testing. If you expect to have let's say 5000 rows in your table, then I'd test for 15 000 and even 50 000 just in case you grow much faster than expected and see what is the best solution (backup plan just in case).

Also you need to consider how many transactions per minutes you expect to have on this table (updates (if any)/selects and inserts). If you expect to have way more selects than inserts, I would use my first method and keep the rankings in the database. Otherwise if you have lots of inserts and few selects then I'd use the temp table idea (actually more of a derived table since it's faster than temp table) and generate the rank on the fly (assuming it is less costly). But then again you'll have to do as many tests as you can come up with and see what best fits your situation.

Also if you have a lot of inserts and a lot of selects, you could maybe keep the new inserts in a temp table and then every 5 to 15 minutes insert that new data in the main table and recount the ranks only once instead of a 100? times.

When in doubt test, test, test and then test again.

Steve Cohen SSC Enthusiast Points: 124 More actions · Answer 12

How about creating a VIEW with the ranking and select from that?

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

He'd be back to square 1... how to generate/hold the rank value?

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

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

or

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

For multiple records, you will remove the where clause and add order by m.name (or 2 for Rank)