How to grab the last value in a column per user for the last date-CodePudding

I have a table that contains three columns: ACCOUNT_ID, STATUS, CREATE_DATE.

I want to grab only the LAST status for each account_id based on the latest create_date.

In the example above, I should only see three records and the last STATUS per that account_2.

Do you know a way to do this?

create table TBL 1 (
account_id int,
status string,
create_date date)

CodePudding user response：

select account_id, max(create_date) from table group by account_id;

will give you the account_id and create_date at the closest past date to today (assuming create_date can never be in the future, which makes sense).

Now you can join with that data to get what you want, something along the lines for example:

select account_id, status, create_date from table where (account_id, create_date) in (<the select expression from above>);

If you use that frequently (account with the latest create date), then consider defining a view for that.

CodePudding user response：

If you have many columns and want keep the row that is the last line, you can use QUALIFY to run the ranking logic, and keep the best, like so:

SELECT * 
FROM tbl
QUALIFY row_number() over (partition by account_id  order by create_date desc) = 1;

The long form is the same pattern the Ely shows in the second answer. But with the MAX(CREATE_DATE) solution, if you have two rows on the same last day, the IN solution with give you both. you can also get via QUALIFY if you use RANK

So the SQL is the same as:

SELECT account_id, status, create_date
FROM (
    SELECT *,
        row_number() over (partition by account_id  order by create_date desc) as rn
    FROM tbl
)
WHERE rn = 1;

So the RANK for, which will show all equal rows is:

SELECT * 
FROM tbl
QUALIFY rank() over (partition by account_id  order by create_date desc) = 1;