Click here to monitor SSC

Finding the 'Nth' highest (or lowest) scorer in a table

Last post 08-19-2012, 8:46 PM by aaronbeck58. 7 replies.
Sort Posts: Previous Next
  •  07-14-2006, 11:20 AM Post number 1214

    Finding the 'Nth' highest (or lowest) scorer in a table

    In another forum, a user came up with the problem that he had a large table, (several million rows) and had to come up with the sixth highest (and lowest) scorer in three columns within around a hundred categories. (I forgot to ask him why he had this problem, but it looked like a kosher commercial application) In its simplest form it might be

    CREATE TABLE [dbo].[ScoreTable](
    
     
    [ScoreTable_ID] [int identity(1,1)] NOT NULL,
     
    [Team_ID] [int] NOT NULL,
     
    [League_ID] [int] NOT NULL,
     
    [Score1] [numeric](3810) NULL,
     
    [Score2] [numeric](3810) NULL,
     
    [Score3] [numeric](2510) NULL
     ) 
    ON [PRIMARY]

    He wanted the sixth highest scorer in score 1 and 2, and the sixth lowest scorer in Score 3 for each Team_id.

    He was doing it in what seemed to me to be a horrendous way, involving iterating through the categories (team and league in my example) using a cursor (sniff) He felt sure it couldn't be done in one SQL Statement. The solution I gave was met with an awful silence, and the  user went away with another, cursor-based, solution. So, the question is what is the best way of doing it? Can we actually benchmark various solutions to come up with the best one?

  •  07-16-2006, 6:05 PM Post number 1267 in reply to post number 1214

    Re: Finding the 'Nth' highest (or lowest) scorer in a table

    I'm not sure the cursor is as bad as you might think in this case.  The issue is, how exactly do you find out which one is the "6th"?  The cursor may do a lot less I/O than any set-based solution (* at least, in SQL Server 2000 -- just like in the recent thread we had regarding showing blanks, in SQL 2005 it gets a bit better (perhaps))...

    Can you provide a script that generates some sample data, and show what the sample output is supposed to look like?  That way we can try various techniques, document them here, and come up with the best way to do it -- consistency will be key to scoring this challenge...


  •  09-01-2006, 5:08 AM Post number 1939 in reply to post number 1267

    Re: Finding the 'Nth' highest (or lowest) scorer in a table

    Well, my initial thought on how to do it in SQL Server 2000 was to get the sixth highest (or lowest with a very slight mod..) in every group, by skimming off the keys for the top five of each group into a table variable and use a NOT IN () to get the sixth. This doesn't really work too well if there is a tie  but otherwise would do the trick.

    Then, of course, if you use identity fields by inserting the 'group by' results into a table, or using  ROW_NUMBER() OVER, (2005 only), this might be more efficient, but this needs to be tested, as what looks slick on the screen isn't necessarily the most efficient once it gets past the Query Optimiser.

     I've done a sketch, using dummy data, to try to illustrate what I meant by my initial idea.....

    SET nocount ON
    DECLARE
    @DummyData TABLE(MyID INT IDENTITY(1,1), name VARCHAR(20), score INT, Team VARCHAR(5))
    INSERT INTO @DummyData SELECT 'fred',5,'blue' INSERT INTO @DummyData SELECT 'mary',2,'blue'
    INSERT INTO @DummyData SELECT 'jane',1,'blue' INSERT INTO @DummyData SELECT 'paul',3,'blue'
    INSERT INTO @DummyData SELECT 'bill',2,'blue' INSERT INTO @DummyData SELECT 'barry',4,'blue'
    INSERT INTO @DummyData SELECT 'garry',6,'blue' INSERT INTO @DummyData SELECT 'Raj',2,'blue'
    INSERT INTO @DummyData SELECT 'brad',5,'blue' INSERT INTO @DummyData SELECT 'Mo',7,'blue'
    INSERT INTO @DummyData SELECT 'joe',12,'blue' INSERT INTO @DummyData SELECT 'mell',8,'blue'
    INSERT INTO @DummyData SELECT 'keith',13,'blue' INSERT INTO @DummyData SELECT 'Shane',9,'blue'
    INSERT INTO @DummyData SELECT 'ian',7,'red' INSERT INTO @DummyData SELECT 'mary',5,'red'
    INSERT INTO @DummyData SELECT 'jane',12,'red' INSERT INTO @DummyData SELECT 'paul',13,'red'
    INSERT INTO @DummyData SELECT 'damian',5,'red' INSERT INTO @DummyData SELECT 'barry',7,'red'
    INSERT INTO @DummyData SELECT 'garry',8,'red' INSERT INTO @DummyData SELECT 'pete',2,'red'
    INSERT INTO @DummyData SELECT 'keith',6,'red' INSERT INTO @DummyData SELECT 'sanji',9,'red'
    INSERT INTO @DummyData SELECT 'joe',15,'red' INSERT INTO @DummyData SELECT 'mell',18,'red'
    INSERT INTO @DummyData SELECT 'keith',13,'red' INSERT INTO @DummyData SELECT 'Shane',19,'red'
    INSERT INTO @DummyData SELECT 'andy',4,'red' INSERT INTO @DummyData SELECT 'Saun',5,'red'

    DECLARE @topRankers TABLE (TheirID INT, iteration INT)
    DECLARE @ii INT
    DECLARE
    @iiMax INT

    SELECT
    @ii=6
    WHILE @ii>0
    BEGIN
    INSERT INTO
    @TopRankers(TheirID, iteration)
    SELECT MyID,@ii FROM @DummyData g INNER JOIN
    (
    SELECT [highscore]=MAX(score), team FROM @DummyData
    WHERE myID NOT IN
    (
    SELECT TheirID FROM @TopRankers
    )
    GROUP BY team
    )f
    ON f.highScore=Score AND f.team=g.team
    SELECT @ii=@ii-1
    END
    SELECT
    name, score, team FROM @dummyData INNER JOIN @TopRankers ON theirID=myID
    WHERE iteration=1

    I suppose the next stage would be to devise a routine to give a decent slab of data, a million or so rows, so as to run some meaningful tests!
  •  09-14-2006, 11:23 AM Post number 2059 in reply to post number 1939

    Re: Finding the 'Nth' highest (or lowest) scorer in a table

    I've probably got the requirement wrong but this gives the same result as your query in a single statement (probably not very efficient though)

    select t.name, t.score, t.team

    from @DummyData t

    join

    (

    select score = min(score), Team

    from @DummyData t

    where score in (select distinct top 6 score from @DummyData t2 where t2.Team = t.Team order by score desc)

    group by Team

    ) t2

    on t.Team = t2.Team

    and t.score = t2.score

  •  09-14-2006, 1:45 PM Post number 2062 in reply to post number 2059

    Re: Finding the 'Nth' highest (or lowest) scorer in a table

    No, you have the got the simplified problem correct. Ingenious, very ingenious. Thanks.

    We now have four different 'cursorless' approaches to the simpler problem of getting the scorer of the sixth highest score in each team.

    1. Nigel's super-slick single statement
    2. My clumsy version
    3. use of identity fields by inserting the 'group by' results into a Temp table,
    4. use of  ROW_NUMBER() OVER, (2005 only)

    It would be interesting as well to see which of the four are the quickest and slowest with a decent sized table.

    It is time I did what Adam asked, and stock the original table with plenty of data. The original Forum query was to come up with the sixth highest (and lowest) scorer in three columns within around a hundred categories. I'll post the 'stocking-routine' when I've done it.

     

  •  01-12-2012, 5:24 AM Post number 105369 in reply to post number 1939

    Re: Finding the 'Nth' highest (or lowest) scorer in a table

    can we find the nth salery from Ranking function like ROW NUMBER()
    EXAMPLE:-
    SELECT TOP 1 SAL FROM
    (SELECT SAL,ROW NUMBER(),OVER(PARTITION BY SAL,ORDER BY SAL AS DESC) AS SL ) FROM EPMLOYEE
    WHERE SL=N
  •  02-01-2012, 10:53 PM Post number 105792 in reply to post number 105369

    Re: Finding the 'Nth' highest (or lowest) scorer in a table

    I think Imran this will work, I studied the same query to find the Nth highest or lowest score in a table.
  •  08-19-2012, 8:46 PM Post number 111541 in reply to post number 105792

    Re: Finding the 'Nth' highest (or lowest) scorer in a table

    It is time I did what Adam asked, and stock the original table with plenty of data. The original Forum query was to come up with the sixth highest (and lowest) scorer in three columns within around a hundred categories.
View as RSS news feed in XML