Using union and order by clause in mysql

Solution 1:

You can do this by adding a pseudo-column named rank to each select, that you can sort by first, before sorting by your other criteria, e.g.:

select *
from (
    select 1 as Rank, id, add_date from Table 
    union all
    select 2 as Rank, id, add_date from Table where distance < 5
    union all
    select 3 as Rank, id, add_date from Table where distance between 5 and 15
) a
order by rank, id, add_date desc

Solution 2:

You can use subqueries to do this:

select * from (select values1 from table1 order by orderby1) as a
union all
select * from (select values2 from table2 order by orderby2) as b

Solution 3:

(select add_date,col2 from table_name) 
  union 
(select add_date,col2 from table_name) 
  union 
(select add_date,col2 from table_name) 

order by add_date

Solution 4:

Don't forget, union all is a way to add records to a record set without sorting or merging (as opposed to union).

So for example:

select * from (
    select col1, col2
    from table a
    <....>
    order by col3
    limit by 200
) a
union all
select * from (
    select cola, colb
    from table b
    <....>
    order by colb
    limit by 300
) b

It keeps the individual queries clearer and allows you to sort by different parameters in each query. However by using the selected answer's way it might become clearer depending on complexity and how related the data is because you are conceptualizing the sort. It also allows you to return the artificial column to the querying program so it has a context it can sort by or organize.

But this way has the advantage of being fast, not introducing extra variables, and making it easy to separate out each query including the sort. The ability to add a limit is simply an extra bonus.

And of course feel free to turn the union all into a union and add a sort for the whole query. Or add an artificial id, in which case this way makes it easy to sort by different parameters in each query, but it otherwise is the same as the accepted answer.

Solution 5:

A union query can only have one master ORDER BY clause, IIRC. To get this, in each query making up the greater UNION query, add a field that will be the one field you sort by for the UNION's ORDER BY.

For instance, you might have something like

SELECT field1, field2, '1' AS union_sort
UNION SELECT field1, field2, '2' AS union_sort
UNION SELECT field1, field2, '3' AS union_sort
ORDER BY union_sort

That union_sort field can be anything you may want to sort by. In this example, it just happens to put results from the first table first, second table second, etc.