Get max dates for each customer

Let’s say I have a customer table like so:

id | start_date | created_at
-----------------------------
1  | 2020-1-15  | 2020-1-15
1  | 2020-1-16  | 2020-1-15
1  | 2020-1-16  | 2020-1-16
2  | 2020-1-15  | 2020-1-15
2  | 2020-1-16  | 2020-1-15

I want to get 1 row per customer id that has the max(start_date) and if it’s the same date will use the max(created_at).

Result should look like this:

id | start_date | created_at
-----------------------------
1  | 2020-1-16  | 2020-1-16
2  | 2020-1-16  | 2020-1-15

I’m having a hard time with window functions as I thought a partition by id would work but I have 2 dates. Maybe I use a group by?

Answer

please try this one, you could use order by two columns

SELECT * FROM (
SELECT id, start_date, Created_At, ROW_NUMBER()OVER(PARTITION BY id ORDER BY start_date DESC, Created_At DESC) AS R
FROM #date
) A
WHERE A.R = 1

Advertisement

Answer