Post reply

Get max row based on the combination of 2 columns

  • 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.

  • 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

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

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