0

I have a table with change history for orders

 cart_id     date                    action
 1           01/01/2020 3:00         approved
 1           01/01/2020 2:00         saved
 2           02/03/2020 9:00         saved
 2           02/03/2020 5:00         created

I need to get a list of all cart IDs where the latest action was "saved". So in this case it would return only cart ID 2 because "saved" was the latest action, whereas the latest action of cart ID 1 was "approved". I'm not really sure where to start here, any way I can achieve this?

Dale K
  • 25,246
  • 15
  • 42
  • 71
Joe Defill
  • 439
  • 1
  • 4
  • 16
  • 1
    Use `row_number()` to find the latest record for each cart, then filter on the action. – Dale K Mar 22 '21 at 00:57
  • Sorry I'm not too familiar with `row_number()`, would this be in the where clause? – Joe Defill Mar 22 '21 at 01:05
  • 1
    Please refer to documentation if you are not familiar with it. https://learn.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql?view=sql-server-ver15 – Squirrel Mar 22 '21 at 01:07
  • Does this answer your question? [how do I query sql for a latest record date for each user](https://stackoverflow.com/questions/2411559/how-do-i-query-sql-for-a-latest-record-date-for-each-user) – Dale K Mar 22 '21 at 01:11

1 Answers1

2

You can use ROW_NUMBER(). For example:

select cart_id
from (
  select cart_id, action,
    row_number() over(partition by cart_id order by date desc) as rn
  from t
) x
where rn = 1 and action = 'saved';
The Impaler
  • 45,731
  • 9
  • 39
  • 76