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.