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.
member_group
table where the in_group_end
value is NULL.member_group
table, with the
value populated for in_group_end
in all of them except the most recent.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.
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;