If i have a table with multiple customer IDs, and each customer has orders on different dates, I need to select each customer and their last 3 orders with their dates.
| Customer | Order_ID | Date | Time | Status | CreatedBy |
|---|---|---|---|---|---|
| A | 1 | 9/29/2021 | 2359 | 1 | john |
| A | 2 | 9/29/2021 | 1028 | 1 | john |
| A | 3 | 1/19/2021 | 0000 | 2 | john |
| A | 4 | 01/18/2021 | 2359 | 2 | adam |
| A | 5 | 01/18/2021 | 0828 | 2 | adam |
| B | 6 | 9/29/2021 | 2359 | 2 | john |
| B | 7 | 9/28/2019 | 1028 | 1 | adam |
| B | 8 | 1/19/2023 | 0300 | 2 | john |
| B | 9 | 1/15/2020 | 0000 | 2 | john |
| B | 10 | 1/10/2017 | 0000 | 1 | john |
| C | 11 | 1/18/2024 | 0000 | 1 | adam |
Result should be like below (need also to select order in status 2 and created by john)
| Customer | Order_ID | Date | Time |
|---|---|---|---|
| A | 1 | 9/29/2021 | 2359 |
| A | 2 | 9/29/2021 | 1028 |
| A | 3 | 1/19/2021 | 0000 |
| B | 8 | 1/19/2023 | 0300 |
| B | 6 | 9/29/2021 | 2359 |
| B | 9 | 1/15/2020 | 0000 |
not getting anywhere with max function while grouping
SELECT tt.* FROM orders tt INNER JOIN (SELECT order_id, MAX(date) AS MaxDateTime FROM order where status='2' and createdby='john' GROUP BY order_id) groupedtt ON tt.order_id = groupedtt.order_id AND tt.date = groupedtt.MaxDateTime
Result should like the below image
New contributor
Momtaz Kalo is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.