Amy Neville Amy Neville - 2 years ago 78
SQL Question

ORDER BY artificial rows created using UNION

I've used a UNION to create an artificial row for "All Countries":

SELECT '0', 'all countries', '10000000000000' AS users
SELECT country_id, country_name, country_internet_users AS users
FROM countries

The problem comes when I try to sort by country_internet_users.

MySQL doesn't seem to behave as expected. It doesn't sort at all. It wouldn't even run the query until I added the "AS users". But despite that it seems to just ignore the ORDER BY completely.

How do I order by a column, when I've added an artificial row using a UNION?

Answer Source

One option is to add a fourth column to each sub select which ranks the order in which each portion of the UNION appears. In your case, you want the first portion of the UNION to appear on top as a sort of table header. Then in the outer query, select only the three columns you really want to appear in your report.

SELECT t.country_id, t.country_name, t.users
    SELECT '0' AS country_id, 'all countries' AS country_name, 10000000000000 AS users, 1 AS val
    SELECT country_id, country_name, country_internet_users AS users, 0 AS val
    FROM countries
) AS t
ORDER BY t.val DESC, t.users DESC

This approach is one way to guarantee that the first half of the UNION will appear on top (assuming that is what you want). As @KubaWyrostek pointed out, if you can rely on the natural ordering of the ID values, then you don't need a subquery.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download