MySQL all unique values from 2 or more columns

Question

I'm trying to select UNIQUE values from a non-uniformized table and show only the unique ones.

SELECT DISTINCT "year" as sorter, year(data) as year, NULL as location
FROM site 
WHERE tipo='projects'

UNION

SELECT DISTINCT "loc" as sorter, NULL as year, spare_1 as location
FROM site 
WHERE tipo='projects'

ORDER BY sorter ASC, year DESC, localition ASC

This would return

+--------+------+----------+
| SORTER | YEAR | LOCATION |
+--------+------+----------+
| year   | 2010 | NULL     |
+--------------------------+
| year   | 2009 | NULL     |
+--------------------------+
| year   | 2008 | NULL     |
+--------------------------+
| loc    | NULL | London   |
+--------------------------+
| loc    | NULL | Paris    |
+--------------------------+
| loc    | NULL | NYC      |
+--------------------------+

I don't really like the double selects. Is there anyway more efficient I can do this?

Thank you in advance.

Answer 1

I think this query may work for you.

SELECT DISTINCT
    CASE WHEN data IS NOT NULL THEN "year" ELSE "loc" END AS sorter,
    CASE WHEN data IS NOT NULL THEN YEAR(data) ELSE NULL END AS year,
    CASE WHEN data IS NULL THEN spare_1 ELSE NULL END AS location
FROM site
WHERE tipo='projects'
ORDER BY sorter ASC, year DESC, localition ASC

The ORDER BY clause doesn't seem to match your sample data, so I left it unchanged.