SQL query for listing values based on a column

I have a table with the following columns member_id, status and created_at (timestamp) and i want to extract the latest status for each member_id based on the timestamp value.

Source: Raw data image

So, my ideal query result would be like this:

Expected query results image

Solution 1:

My go to process for doing things like that is to assign a row number to each data and get row number 1 depending on the partition and sorting.

For mysql, this is only available starting mysql 8

SELECT ROW_NUMBER() OVER(PARTITION BY member_id ORDER BY created_at DESC) as row_num, 
member_id, status, created_at FROM table

This will generate something like this.

Then you use that as a sub query and get the rows where row_num = 1

SELECT member_id, status, created_at FROM (
    SELECT ROW_NUMBER() OVER(PARTITION BY member_id ORDER BY created_at DESC) as row_num, 
    member_id, status, created_at FROM table
) a WHERE row_num = 1