Using union and order by clause in mysql

Question

I want to use order by with union in mysql query. I am fetching different types of record based on different criteria from a table based on distance for a search on my site. The first select query returns data related to the exact place search . The 2nd select query returns data related to distance within 5 kms from the place searched. The 3rd select query returns data related to distance within 5-15 kms from the place searched.

Then i m using union to merge all results and show on a page with paging. Under appropriate heading as 'Exact search results', 'Results within 5 kms' etc

Now i want to sort results based on id or add_date. But when i add order by clause at the end of my query ( query1 union query 2 union query 3 order by add_date). It sorts all results. But what i want is it should sort under each heading.

Answer 1

This is because You're sorting entire result-set, You should sort, every part of union separately, or You can use ORDER BY (Something ie. subquery distance) THEN (something ie row id) clause

Answer 2

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.

Answer 3

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.