Having customers and orders they made, I am trying to get all sets of three consecutive values of a column based on the date.
For instance, it should return the below for a customer
-------------+------------+------------------------------+
| CustomerID | OrderType | OrderDate |
-------------+------------+------------------------------+
| 1010 | 193 | 2022-09-10 09:54:04.380 |
| 1010 | 130 | 2022-09-09 09:04:21.120 |
| 1010 | 125 | 2022-09-05 00:00:55.947 |
-------------+------------+------------------------------+
It should not return these ones
-------------+------------+------------------------------+
| CustomerID | OrderType | OrderDate |
-------------+------------+------------------------------+
| 1020 | 193 | 2022-09-10 09:54:04.380 |
| 1020 | 185 | 2022-09-08 10:04:21.120 |
| 1020 | 130 | 2022-09-03 10:13:25.597 |
| 1020 | 125 | 2022-09-01 01:00:15.523 |
-------------+------------+------------------------------+
OrderType returned should be only these ones: 125, 130, 193
I tried this one but not sure how to change it to return correct rows
SELECT tmp.CustomerID, tmp.OrderType, tmp.OrderDate
FROM ( SELECT O.CustomerID, O.OrderType, O.OrderDate,
ROW_NUMBER() OVER (PARTITION BY O.CustomerID
ORDER BY O.OrderDate desc, O.OrderType DESC
) AS rn
FROM Orders O
WHERE O.OrderType in (125, 130, 193)
) tmp
WHERE tmp.rn <= 3
ORDER BY tmp.CustomerID DESC, tmp.rn DESC