Aggregates excluding leaders in MySQL

-- ---------------------------------------------------------------------------
-- Aggregates excluding leaders
--
-- from the Artful Common Queries page
-- http://www.artfulsoftware.com/infotree/qrytip.php?id=633
-- ---------------------------------------------------------------------------

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