You have a table of grouped ranks and you wish to list ranks by group omitting the leading rank in each group. The simplest query for group leaders is...

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