Search code examples
mysqlsqlselectsubquery

Join results of Duplicate column name 'year'

I want something like this (working example):

SELECT DISTINCT year FROM
    (SELECT year FROM classbook WHERE year > 1
        UNION ALL SELECT year + 1 as year FROM classbook WHERE year > 1) qu
ORDER BY year

I tried:

SELECT DISTINCT year FROM
    (SELECT year, year + 1 as year FROM classbook WHERE year > 1) qu
ORDER BY year

But this throws Duplicate column name 'year'.

What's the simplest (a simpler) way to accomplish this?

I'm using MySQL database.

Solution

  • Your first query is fine. You don't need a subquery:

    SELECT year
FROM classbook
WHERE year > 1
UNION   -- on purpose to remove duplicates
SELECT year + 1 as year
FROM classbook
WHERE year > 1
ORDER BY year