Order subquery results and return first row per ID

I have a MySQL DB with a person table, where I store the personal details of users. Each user belongs to a group, so I have another member_group table where I store their user ID against their group ID, along with their in_group_begin date and (eventually) their in_group_end date.

When a user is in a group, the in_group_end value is null. When they leave that group, it gets populated with the date of departure. They may then be gone forever, or just move to another group necessitating a new row in the member_group table.

So TOM, who has just joined, may have one record in the member_group table where the in_group_end value is NULL.
DICK might have been here a while and has four records in the member_group table, with the value populated for in_group_end in all of them except the most recent.
And HARRY has been and gone - he as two records in the member_group table, with a value populated in the in_group_end column for all of them.

I now wish to query the person table and return a list of my users with - in the case of current users - their current group ID OR - in the case of past users - the last group ID they were in.

I presume this can be resolved in a subquery, but I'm not sure how to order ( e.g. ORDER BY (in_group_end = NULL) DESC, in_group_end DESC ) and then also GROUP BY, which would execute beforehand anyway. The member_group table has an auto-incrementing tbl_id field, so maybe some kind of further join on MAX(tbl_id) or something?

What would this query look like? Any help appreciated.

Solution

You can use FIRST_VALUE() window function:

SELECT DISTINCT user_id,
       FIRST_VALUE(group_id) OVER (
         PARTITION BY user_id 
         ORDER BY in_group_end IS NULL DESC, in_group_end DESC
       ) AS last_group_id
FROM member_group;

If you want all the details of the users:

WITH cte AS (
    SELECT DISTINCT user_id,
           FIRST_VALUE(group_id) OVER (
             PARTITION BY user_id 
             ORDER BY in_group_end IS NULL DESC, in_group_end DESC
           ) AS last_group_id
    FROM member_group
)
SELECT p.*, c.last_group_id
FROM person AS p LEFT JOIN cte AS c -- LEFT join just in case a person was never a member of any group
ON c.user_id = p.user_id;

If you want the last in_group_end value also, it is easier with ROW_NUMBER() window function:

WITH cte AS (
    SELECT *,
           ROW_NUMBER() OVER (
             PARTITION BY user_id 
             ORDER BY in_group_end IS NULL DESC, in_group_end DESC
           ) AS rn
    FROM member_group
)
SELECT p.*, 
       c.user_id, c.in_group_end, c.group_id AS last_group_id
FROM person AS p LEFT JOIN cte AS c -- LEFT join just in case a person was never a member of any group
ON c.user_id = p.user_id AND c.rn = 1;