eroge_sql2.sql

SELECT
  to_char(g.sellday, 'YYYY') "year",
  g.sellday,
  g.gamename,
  g.median
FROM gamelist g
INNER JOIN (
  SELECT
    to_char(g2.sellday, 'YYYY') "year",
    MAX(g2.median) "median"
  FROM gamelist g2
  WHERE
    g2.sellday BETWEEN to_date('2004', 'YYYY') AND to_date('2015', 'YYYY')
    AND
      g2.gamename LIKE '%妹%'
    GROUP BY
     to_char(g2.sellday, 'YYYY')
) t
  ON
    to_char(g.sellday, 'YYYY') = t.year
WHERE
  g.median = t.median
  AND
    g.gamename LIKE '%妹%'
ORDER BY
  g.sellday;