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

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 2

I got this working on a join plus union.

(SELECT 
   table1.column1,
   table1.column2,
   foo1.column4
 FROM table1, table2, foo1, table5
 WHERE table5.somerecord = table1.column1
 ORDER BY table1.column1 ASC, table1.column2 DESC
)

UNION

(SELECT
    ... Another complex query as above
)

ORDER BY column1 DESC, column2 ASC

Answer 3

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.

Answer 4

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

Answer 5

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 6

(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