MySQL Select first entry of GROUP after custom ORDER BY

user_16631012 0
Viewed 43

I'm trying to fetch the first entry of each group after the custom ORDER BY but don't know how to select that first entry of each group. The groups should be ordered by the @team if exist, otherwise other team else NULL.

SELECT t1.*
FROM tbl t1
INNER JOIN tbl2 t2 ON t2.group_id = t1.group
WHERE t2.region = @region
ORDER BY
CASE
  WHEN team=@team THEN 1
  WHEN team is NOT NULL THEN 2
  WHEN team is NULL THEN 3
END

Content of tbl

id  group   team
1   1       AA
2   1       BB
3   2       AA
4   2       CC
5   3       BB
6   3       NULL
7   4       NULL

Expected result when @team=AA

id  group   team
1   1       AA
3   2       AA
5   3       BB
7   4       NULL

Expected result when @team=BB

id  group   team
2   1       BB
3   2       AA
5   3       BB
7   4       NULL
mysql
1 Answers

Use your custom ordering logic with ROW_NUMBER:

WITH cte AS (
    SELECT t1.*, ROW_NUMBER() OVER (PARTITION BY t1.`group`
                                    ORDER BY CASE WHEN team = @team THEN 1
                                                  WHEN team IS NOT NULL THEN 2
                                                  ELSE 3 END) rn
    FROM tbl t1
    INNER JOIN tbl2 t2 ON t2.group_id = t1.`group`
    WHERE t2.region = @region
)

SELECT id, `group`, team
FROM cte
WHERE rn = 1;

Side note: Avoid naming your table columns GROUP, as this is a reserved MySQL keyword, and therefore must always be escaped in backticks.

user_18632290
Related