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 );

and 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 |
+------+------+


Return to the Artful Common Queries page