TOP 3 DISTINCT

• ShuaibV

  SSCrazy

  Points: 2001

  More actions

  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

  StatusID

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

  Active1291070

  Active1242253

  Active1227193

  Active948452

  Suspended-Occupancy698055

  Active565330

  Suspended-Payment Pending545343

  Output

  StatusID

  Active1291070

  Suspended-Occupancy698055

  Suspended-Payment Pending545343

  Thanks

  Shuaib

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  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

• ShuaibV

  SSCrazy

  Points: 2001

  More actions

  April 28, 2009 at 1:52 pm

  #985451

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

  Thanks

  Shuaib

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  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

• ShuaibV

  SSCrazy

  Points: 2001

  More actions

  April 28, 2009 at 3:56 pm

  #985548

  This query works for me.

  Thanks

  Shuaib