mysqlwindow-functionsmariadb-10.5

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.

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;