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.

Thanks in advance.

Answer 1

Try:

SELECT result.* 
FROM (
 [QUERY 1]
 UNION
 [QUERY 2]
) result
ORDER BY result.id

Where [QUERY 1] and [QUERY 2] are your two queries that you want to merge.

Answer 2

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 3

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 betwen 5 and 15
) a
order by rank, id, add_date desc

Answer 4

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 5

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

Answer 6

I tried adding the order by to each of the queries prior to unioning like

(select * from table where distance=0 order by add_date) union (select * from table where distance>0 and distance<=5 order by add_date)

but it didn't seem to work. It didn't actually do the ordering within the rows from each select.

I think you will need to keep the order by on the outside and add the columns in the where clause to the order by, something like

(select * from table where distance=0) union (select * from table where distance>0 and distance<=5) order by distance, add_date

This may be a little tricky, since you want to group by ranges, but I think it should be doable.