This is a variation on the "greatest-n-per-group" problem. It could be used with window functions or
LATERAL joins (also known as
CROSS/OUTER APPLY), if only MySQL had implemented them*.
Here's one method that works. I call it "poor man's cross apply":
( select distinct color from tbl ) as td -- for every colour
left join -- join to the table
tbl as t -- and get all rows
on t.color = td.color -- with that color
and t.sn >= coalesce( -- and sn bigger or equal than
( select ti.sn -- the 2nd higher sn value
from tbl as ti
where ti.color = td.color
order by ti.sn desc
limit 1 offset 1 -- 1 + 1st = 2nd
), -9223372036854775808 -- the smallest possible bigint value,
) ; -- to cover cases of colours
-- with a single appearance, where
-- the subquery returns NULL
Tested in dbfiddle.uk. An index on
(color, sn) or
(color, sn, value) will be used by this query. If there are only a few distinct
color values, it is quite efficient.
*: MariaDB, a fork of MySQL has indeed implemented window functions, so a solution with a ranking function, like
DENSE_RANK() would work there.