Using union and order by clause in mysql

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.

 Answers

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

---

(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

---

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.

---

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.

---

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.