By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE store_control
(
Location int
, Aisle int
, TStamp datetime
, Qty int
, Time int
)
;
INSERT INTO store_control
VALUES
(12,420 , '2021-04-16 12:22:01 PM', 999, 999 )
, (23 , 220 , '2021-04-16 11:22:01 PM' , 8888 , 222 )
, (31 , 310 , '2021-04-16 10:22:01 PM' , 666 , 333 )
, (12 , 420 , '2021-04-16 12:22:01 AM' , 666 , 444 )
, (31 , 120 , '2021-04-16 3:22:01 PM' , 666 , 555 )
, (22 , 210 , '2021-04-16 01:22:01 PM' , 666 , 666 )
6 rows affected
;WITH cte AS
(
SELECT *
, ROW_NUMBER() OVER(PARTITION BY Location, Aisle ORDER BY Location, TStamp DESC, Time DESC) AS RowNum
FROM store_control
)
SELECT *
FROM cte
WHERE RowNum = 1
| Location | Aisle | TStamp | Qty | Time | RowNum |
|---|---|---|---|---|---|
| 12 | 420 | 2021-04-16 12:22:01.000 | 999 | 999 | 1 |
| 22 | 210 | 2021-04-16 13:22:01.000 | 666 | 666 | 1 |
| 23 | 220 | 2021-04-16 23:22:01.000 | 8888 | 222 | 1 |
| 31 | 120 | 2021-04-16 15:22:01.000 | 666 | 555 | 1 |
| 31 | 310 | 2021-04-16 22:22:01.000 | 666 | 333 | 1 |