Post reply

TOP 3 DISTINCT

  • April 28, 2009 at 11:56 am

    #220674

    Hi

    Can someone help to get the TOP 3 distinct output based on the below input? ID column is identity column

    Input

    Status ID

    ----------------

    Active 1291070

    Active 1242253

    Active 1227193

    Active 948452

    Suspended-Occupancy 698055

    Active 565330

    Suspended-Payment Pending 545343

    Output

    Status ID

    Active 1291070

    Suspended-Occupancy 698055

    Suspended-Payment Pending 545343

    Thanks

    Shuaib

  • April 28, 2009 at 12:09 pm

    #985369

    Do you want the largest ID for each status?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 28, 2009 at 1:52 pm

    #985451

    Yep, I want to pick the largest ID for each status.

    Thanks

    Shuaib

  • April 28, 2009 at 1:56 pm

    #985457

    Try this.

    SELECT TOP 3 Status, MaxID AS ID

    FROM

    (SELECT Status, MAX(ID) AS MaxID FROM SomeTable GROUP BY Status) sub

    ORDER BY Status

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 28, 2009 at 3:56 pm

    #985548

    This query works for me.

    Thanks

    Shuaib

Viewing 5 posts - 1 through 4 (of 4 total)

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