want to retrieve rows with maximum values for a particular column

• de4ever

  SSC Journeyman

  Points: 97

  More actions

  December 1, 2008 at 10:57 pm

  #73268

  The table is somewhat like this

  Create Table #tab1(

  [HistoryID] [int] IDENTITY(1,1),

  [IssueID] [int] NULL,

  [UserID] [int] NULL,

  [CreatedDate] [datetime] NULL,

  )

  Insert #Tab1 values(1,3,'2008-09-24 18:13:56.000')

  Insert #Tab1 values(1,3,'2008-09-25 18:13:56.000')

  Insert #Tab1 values(2,4,'2008-09-26 18:13:56.000')

  Insert #Tab1 values(2,4,'2008-09-22 18:13:56.000')

  Insert #Tab1 values(2,4,'2008-09-28 18:13:56.000')

  Insert #Tab1 values(2,4,'2008-09-29 18:13:56.000')

  select * from #Tab1

  will display

  HistoryId IssueID UserID CreatedDate

  1 1 3 2008-09-24 18:13:56.000

  2 1 3 2008-09-25 18:13:56.000

  3 2 4 2008-09-26 18:13:56.000

  4 2 4 2008-09-22 18:13:56.000

  5 2 4 2008-09-28 18:13:56.000

  6 2 4 2008-09-29 18:13:56.000

  i want all the rows with largest createddate group by IssueId and UserID..ie..

  i want one row each for each same issueid and userid with largest createdDate

  Please Help...

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  December 1, 2008 at 11:09 pm

  #906037

  Like this:

  Select IssueId, UserID, MAX(CreatedDate)

  From #tab1

  Group By IssueId, UserID

  [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
  Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

• brainy

  SSCrazy

  Points: 2445

  More actions

  December 1, 2008 at 11:12 pm

  #906039

  Try this statement :

  select issueid, userid, max(createddate) from #tab1 group by issueid, userid

• de4ever

  SSC Journeyman

  Points: 97

  More actions

  December 1, 2008 at 11:34 pm

  #906045

  Thanks for the replies i found the solution....