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.
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