Post reply

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

  • 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

  • February 7, 2005 at 9:46 am

    #540983

    Couple of alternatives in this thread:

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

     

  • February 7, 2005 at 9:52 am

    #540984

    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.

  • February 7, 2005 at 10:03 am

    #540988

    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.

  • February 7, 2005 at 2:18 pm

    #541022

    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]

  • February 8, 2005 at 2:07 am

    #541075

    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?

  • February 8, 2005 at 2:14 am

    #541076

    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]

  • February 8, 2005 at 8:08 am

    #541125

    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

  • February 8, 2005 at 8:12 am

    #541128

    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?

  • February 8, 2005 at 8:27 am

    #541131

    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

  • February 8, 2005 at 8:30 am

    #541135

    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.

  • February 8, 2005 at 9:05 am

    #541151

    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.

  • February 8, 2005 at 11:23 am

    #541200

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

  • February 8, 2005 at 11:30 am

    #541202

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

  • February 8, 2005 at 11:45 am

    #541208

    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)

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

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