SQL Server Performance

Finding the TOP values based on some Condetion

Discussion in 'General Developer Questions' started by josephmp, May 20, 2006.

  1. josephmp New Member

    I have a table 'PayTable' with fields Code, Collection and Rank

    CREATE TABLE PayTable
    (
    Code varchar(10) NULL,
    Collection decimal NULL,
    Rank varchar(10) NULL
    )

    INSERT PayTable VALUES('101', 500, 'R1')
    INSERT PayTable VALUES('102', 625, 'R2')
    INSERT PayTable VALUES('103', 750, 'R1')
    INSERT PayTable VALUES('104', 125, 'R1')
    INSERT PayTable VALUES('105', 175, 'R2')
    INSERT PayTable VALUES('106', 450, 'R2')
    INSERT PayTable VALUES('107', 825, 'R2')
    INSERT PayTable VALUES('108', 445, 'R1')
    INSERT PayTable VALUES('109', 735, 'R2')
    INSERT PayTable VALUES('110', 930, 'R1')
    INSERT PayTable VALUES('111', 975, 'R2')


    The Rank field can contain any values ie R1, R2, R3....
    Code field is the Primary Key

    I need a simple sql statement without using cursers that gives me the Top 2 Collections, Code and Rank in Each Ranks given in the Table
    ie my output should be
    930 110 R1
    750 103 R1
    975 111 R2
    825 107 R2

    my Query returns only the top 2 values avaliable in the list

    select TOP 2(Collection), Code, Rank from PayTable
    group by Collection, Code, Rank
    order by Collection desc

    Please Help
  2. Madhivanan Moderator


    Select Collection,Code,Rank from PayTable T
    where code in
    (
    select top 2 code from @PayTable
    where Rank=T.Rank order by Collection DESC
    )
    order by Rank,Collection DESC


    Madhivanan

    Failing to plan is Planning to fail
  3. josephmp New Member

    Thanks for the Reply.....

    I want to give different values to the TOP key word ie when i write this in a stored procedure.
    Sometimes i want to get top 3, 4, 5 etc
    please help
  4. Madhivanan Moderator

    Only way is to use Dyanic SQL


    Declare @TopVar int
    Set @TopVar=3
    EXEC('
    Select Collection,Code,Rank from @PayTable T
    where code in
    (
    select top '+@TopVar+' code from @PayTable
    where Rank=T.Rank order by Collection DESC
    )
    order by Rank,Collection DESC')




    Madhivanan

    Failing to plan is Planning to fail
  5. nigelrivett New Member

    Something like this will do it without dynamic sql.
    I'm assuming that you can have duplicate collections within a rank but not collection, code.
    Take out the second bit of the num calculation if collection is unique.

    declare @i int
    select @i = 3
    select Rank, Collection, Code
    from
    (
    select Rank, Collection, Code,
    num = (select count(*) from PayTable t2 where t2.Rank = t1.Rank and (t2.Collection > t1.Collection or (t2.Collection = t1.Collection and t2.Code > t1.Code))) + 1
    from PayTable t1
    ) ord
    where num <= @i
    order by Rank, Collection desc, code desc

  6. josephmp New Member

    i have inserted one more value to the table

    INSERT PayTable VALUES('115', 500, 'R1')

    and want the top 2 values from each rank

    if i use the dsql by Madhivanan i will get the result of Rank R1 as
    750103R1
    500101R1

    and that of nigelrivett i will get the result as
    R1750103
    R1500115

    There is a difference of employ code 101 and 115 in the queries

    If there is more than two entry for the top two positions (ie there is a repatition for the top collection) where employ 101 and 115 have both collection of 500 each and both are eligible for second spot, then how can i modify the query to include those values.

    i need the result as
    R1750103
    R1 500 101
    R1500115
    R2825107
    R2625102
    R3948113
    R3945114


    Please Help


  7. nigelrivett New Member

    That's because the code I gave takes the top two in order of collection then code.
    Madhivanan gives it in order of collection only - order of code is indeterminate and could change from run to run.

    for all codes for top two collections

    declare @i int
    select @i = 3
    select Rank, Collection, Code
    from
    (
    select Rank, Collection, Code,
    num = (select count(*) from PayTable t2 where t2.Rank = t1.Rank and (t2.Collection > t1.Collection) + 1
    from PayTable t1
    ) ord
    where num <= @i
    order by Rank, Collection desc, code desc

  8. nigelrivett New Member

    for the dynamic sql

    Declare @TopVar int
    Set @TopVar=3

    EXEC('Select Collection,Code,Rank
    from @PayTable T
    where collection in (select top '+@TopVar+' collection from @PayTable
    where Rank=T.Rank order by collection DESC)
    order by Rank,Collection DESC')
  9. josephmp New Member

    Thanks for the help Mr. nigelrivett

    The Query does not fully solve my problem

    The Table may contain thousands of entries and if i try to take the top 5 entries then if the top most spot is occupied by collection say 999 and 10 or more entries have this same collection value for a particular rank and all these entries are eligible for top position.
    Even if i took top 5 i need all the eligible entries in the top 5 spot for each rank.
    ie if there is more than 1 entries for the 5th position then the query should show those entries also.
    So each rank can contain 5, 6... entried depending on the top 5 entries collection values


    Please Help
  10. nigelrivett New Member

    That will give all the entries in the top 5 collections.
    It sounds like you want the top 5 entries and ties ordered by collection.

    wrong
    it does give the top 5 entries and ties.
  11. nigelrivett New Member

    Not sure what you want:
    That gave the top 5 entries and ties ordered by collection

    If you look at the subquery that gives the order

    select Rank, Collection, Code,
    num = (select count(*) from PayTable t2 where t2.Rank = t1.Rank and (t2.Collection > t1.Collection or (t2.Collection = t1.Collection and t2.Code > t1.Code))) + 1
    from PayTable t1
    order by Rank, num

    You will see that this gives the sequence for each row.

    select Rank, Collection, Code,
    num = (select count(*) from PayTable t2 where t2.Rank = t1.Rank and (t2.Collection > t1.Collection)) + 1
    from PayTable t1
    order by Rank, num

    This gives the same value for each entry in the collection - the next collection starting from the number in the preceeding :

    so
    declare @i int
    select @i = 3
    select Rank, Collection, Code
    from
    (
    select Rank, Collection, Code,
    num = (select count(*) from PayTable t2 where t2.Rank = t1.Rank and (t2.Collection > t1.Collection)) + 1
    from PayTable t1
    ) ord
    where num <= @i
    order by Rank, Collection desc, code desc

    will give ties.

  12. josephmp New Member

    My actual scenario is I want to give a Prize to top 5 (may vary) employees who have brought the top most collection. So i can't avoid the 6th member who also have the same collection as that of the fifth member.
  13. nigelrivett New Member

    Not sure what the problem is.
    That will include ties.
    If you have 5 in the top collection and 4 in the next it will give the top 5 for 1 to 5 then include the next 4 if you select 6.
  14. josephmp New Member

    if there are 8 members who have exactly the same collection and also this collection is the top most collection of the rank, i need the top 5 members then if i work with the prev: query i have to avoid 3 members who are also eligible for the top 5 position. So even if i take top 5 i need to get all the 8 for that rank and for other ranks i need to get only 5 codes(where no such situations exists)

    Please help me
  15. nigelrivett New Member

    >> if there are 8 members who have exactly the same collection and also this collection is the top most collection of the rank, i need the top 5 members then if i work with the prev: query i have to avoid 3 members who are also eligible for the top 5 position.

    That's the first query I gave - it gives 5 entries per rank only.

    You seem a bit confused about the requirement though. You said in the previous posts

    >> if there is more than 1 entries for the 5th position then the query should show those entries also.
    So each rank can contain 5, 6... entried depending on the top 5 entries collection values

    >> My actual scenario is I want to give a Prize to top 5 (may vary) employees who have brought the top most collection. So i can't avoid the 6th member who also have the same collection as that of the fifth member.

    So you were saying you wanted the top entries and all those in the same collections.
    Now you are saying you just want the top entries.

    To help you clarify try this situation.

    CREATE TABLE PayTable
    (
    Code varchar(10) NULL,
    Collection decimal NULL,
    Rank varchar(10) NULL
    )

    INSERT PayTable VALUES('101', 503, 'R1')
    INSERT PayTable VALUES('102', 502, 'R1')
    INSERT PayTable VALUES('103', 502, 'R1')
    INSERT PayTable VALUES('104', 502, 'R1')
    INSERT PayTable VALUES('105', 501, 'R1')

    You select 3 entries
    Do you want codes 101, 104, 105 (that's the first query I gave).
    or do you want 101, 102, 103, 104 (that's the last query I gave).

    The two queries are
    declare @i int
    select @i = 3
    select Rank, Collection, Code
    from
    (
    select Rank, Collection, Code,
    num = (select count(*) from PayTable t2 where t2.Rank = t1.Rank and (t2.Collection > t1.Collection or (t2.Collection = t1.Collection and t2.Code > t1.Code))) + 1
    from PayTable t1
    ) ord
    where num <= @i
    order by Rank, Collection desc, code desc

    declare @i int
    select @i = 3
    select Rank, Collection, Code
    from
    (
    select Rank, Collection, Code,
    num = (select count(*) from PayTable t2 where t2.Rank = t1.Rank and (t2.Collection > t1.Collection)) + 1
    from PayTable t1
    ) ord
    where num <= @i
    order by Rank, Collection desc, code desc
  16. FrankKalis Moderator

  17. josephmp New Member

    Thanks a Lot Experts For the Help
    The query sent by nigelrivett is exactly what i wanted.
    My Database is SQL Server 2000


    Joseph
  18. Madhivanan Moderator

    Nigel's code is better in performancewise than the code I posted

    Madhivanan

    Failing to plan is Planning to fail

Share This Page