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

CodePudding user response：

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

CodePudding user response：

MySQL has support for Window Function since v8.0. the solution from crimson589 is preferred for v8 , this solution applies for earlier versions of MySQL or if you need an alternate solution to window queries.

After grouping by member_id we can either join back into the original set to gain the corresponding status value to the MAX(created_at)

SELECT ByMember.member_id
, status.status
, ByMember.created_at
FROM (
  SELECT member_id, max(created_at) as created_at
  FROM MemberStatus
  GROUP BY member_id
  ) ByMember
JOIN MemberStatus status ON ByMember.member_id = status.member_id AND ByMember.created_at = status.created_at;

Or you could use a sub query instead of the join:

SELECT ByMember.member_id
, (SELECT status.status FROM MemberStatus status WHERE ByMember.member_id = status.member_id AND ByMember.created_at = status.created_at) as status
, ByMember.created_at
FROM (
  SELECT member_id, max(created_at) as created_at
  FROM MemberStatus
  GROUP BY member_id
  ) ByMember

The JOIN based solution allows you to query additional columns from the original set instead of having multiple sub-queries. I would almost always advocate for the JOIN solution, but sometimes the sub-query is simpler to maintain.

I've setup a fiddle to compare these options: http://sqlfiddle.com/#!9/0edb931/11

CodePudding user response：

You can group by member_id and max of created_at, then a self join with member_id and created_at will give you the latest status.