Post reply

Selecting the top two for a group.

  • August 28, 2003 at 2:34 pm

    #80875

    Here's my data, user is always unique. There's a large volume of data and we update it frequently, so no temp tables.

    At any given time I want to know let say the top 2 users based on Qty for all groups.

    user qty group

    1 2456 1

    2 861 1

    3 1042 1

    4 254 2

    5 10 2

    6 2458 2

    etc

    John Zacharkan


    John Zacharkan

  • August 28, 2003 at 3:00 pm

    #471880

    Here is a query that would work for you, but is slow on large sets if no index is available. I would create a composite index on group, qty, where qty is in desc sequence to speed this up.

    declare @t table ( int, qty int, [group] int)

    insert into @t values(1, 2456, 1)

    insert into @t values(2, 861, 1)

    insert into @t values(3, 1042, 1)

    insert into @t values(4, 254, 2)

    insert into @t values(5, 10, 2)

    insert into @t values(6, 2458, 2)

    select * from @t a

    where in (select top 2 from @t

    where a.[group] = [group]

    order by qty desc)

    Gregory Larsen, DBA

    If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

    Gregory A. Larsen, MVP

  • August 29, 2003 at 11:45 am

    #472024

    Thanks Greg,

    John Zacharkan


    John Zacharkan

Viewing 3 posts - 1 through 2 (of 2 total)

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