Artful Common Queries

Aggregates excluding leaders

from the Artful Common Queries page

You have a table of grouped ranks ...

DROP TABLE IF EXISTS grps,ranks;
CREATE TABLE grps (grp int);
INSERT INTO grps VALUES(1),(2),(3),(4);
CREATE TABLE ranks(grp int,`rank` int);
INSERT INTO ranks VALUES
(1, 4 ),(1, 7 ),(1, 9 ),(2, 2 ),(2, 3 ),(2, 5 ),
(2, 6 ),(2, 8 ),(3, 1 ),(4,11 ),(4,12 ),(4,13 );

Note that since version 8.0, rank is a reserved word and needs to be backticked. You wish to list ranks by group omitting the leading rank in each group. The simplest query for group leaders is ...

SELECT grp, MIN(`rank`) as top 
FROM ranks r2
GROUP BY grp
+------+------+
| grp  | top  |
+------+------+
|    1 |    4 |
|    2 |    2 |
|    3 |    1 |
|    4 |   11 |
+------+------+

The simplest way to get a result that omits these is an exclusion join from the ranks table to the above result:

SELECT r1.grp, r1.`rank`
FROM ranks r1
LEFT JOIN (
  SELECT grp, MIN(`rank`) as top 
  FROM ranks r2
  GROUP BY grp
) AS r2 ON r1.grp=r2.grp AND r1.`rank`=r2.top
WHERE r2.grp IS NULL
ORDER BY grp, `rank`;
+------+------+
| grp  | rank |
+------+------+
|    1 |    7 |
|    1 |    9 |
|    2 |    3 |
|    2 |    5 |
|    2 |    6 |
|    2 |    8 |
|    4 |   12 |
|    4 |   13 |
+------+------+

Last updated 31 May 2024