Get max row based on the combination of 2 columns

• SQLCurious

  SSCrazy

  Points: 2339

  More actions

  April 1, 2014 at 4:20 pm

  #284107

  I have the following data in my table. The last 2 columns - OrderDate and OrderTime - come from Date and Time dimension tables. They represent YYYYMMDD and the "Second of the day" respectively (starts with 1st second and goes all the way to 86,400 for the last second of the day).

  -- Declare sample table

  DECLARE @MySampleOrders TABLE (OrderID INT, ItemType INT, ItemQty INT, OrderDate INT, OrderTime INT)

  -- Insert sample data

  INSERT @MySampleOrders VALUES

  (1001, 201, 12, 20140120, 81389),

  (1001, 202, 25, 20140120, 84805),

  (1001, 203, 75, 20140120, 85303),

  (1001, 201, 10, 20140121, 8828),

  (1001, 202, 23, 20140121, 9276),

  (1001, 204, 33, 20140121, 9519),

  (1001, 202, 21, 20140123, 9747),

  (1001, 202, 16, 20140124, 10386),

  (1020, 331, 13, 20140121, 76699),

  (1020, 413, 15, 20140121, 76845),

  (1020, 502, 21, 20140125, 11981),

  (1020, 331, 72, 20140125, 12186),

  (1020, 413, 39, 20140127, 77647),

  (1020, 331, 44, 20140127, 77786)

  SELECT * FROM @MySampleOrders

  I am looking to come up with a query that outputs the most recent entry (based on OrderDate and OrderTime columns) for each of the OrderID and ItemType combination. From the sample data set, I expect only the values from the second set to be returned.

  Thanks in advance.

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  April 1, 2014 at 4:25 pm

  #1702275

  Something like this?

  WITH CTE AS(

  SELECT *,

  ROW_NUMBER() OVER( PARTITION BY OrderID, ItemType ORDER BY OrderDate DESC, OrderTime DESC) rn

  FROM @MySampleOrders

  )

  SELECT *

  FROM CTE

  WHERE rn = 1

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2