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

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 3

(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

Answer 4

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.

Answer 5

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 6

When you use an ORDER BY clause inside of a sub query used in conjunction with a UNION mysql will optimise away the ORDER BY clause.

This is because by default a UNION returns an unordered list so therefore an ORDER BY would do nothing.

The optimisation is mentioned in the docs and says:

To apply ORDER BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the SELECT:

(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10) UNION
(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);

However, use of ORDER BY for individual SELECT statements implies nothing about the order in which the rows appear in the final result because UNION by default produces an unordered set of rows. Therefore, the use of ORDER BY in this context is typically in conjunction with LIMIT, so that it is used to determine the subset of the selected rows to retrieve for the SELECT, even though it does not necessarily affect the order of those rows in the final UNION result. If ORDER BY appears without LIMIT in a SELECT, it is optimized away because it will have no effect anyway.

The last sentence of this is a bit misleading because it should have an effect. This optimisation causes a problem when you are in a situation where you need to order within the subquery.

To force MySQL to not do this optimisation you can add a LIMIT clause like so:

(SELECT 1 AS rank, id, add_date FROM my_table WHERE distance < 5 ORDER BY add_date LIMIT 9999999999)
UNION ALL
(SELECT 2 AS rank, id, add_date FROM my_table WHERE distance BETWEEN 5 AND 15 ORDER BY rank LIMIT 9999999999)
UNION ALL
(SELECT 3 AS rank, id, add_date from my_table WHERE distance BETWEEN 5 and 15 ORDER BY id LIMIT 9999999999)

A high LIMIT means that you could add an OFFSET on the overall query if you want to do something such as pagination.

This also gives you the added benefit of being able to ORDER BY different columns for each union.

Answer 7

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 8

i was trying (order by after union) for below query, but couldn't:

select table1.*,table2.* 
from table1 
left join table2 on table2.id=0 
   union
select table1.*,table2.* 
from table2 
left join table1 on table2.i=table1.id
order by table1.id;

whatever your query is,
just add a same name alias in column, in all your select union queries
in above example it will be:

select table1.id as md,table1.*,table2.* 
from table1 
left join table2 on table2.id=0 
   union
select table1.id as md,table1.*,table2.* 
from table2 
left join table1 on table2.i=table1.id
order by md;

Answer 9

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 10

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 11

Just use order by column number (don't use column name). Every query returns some columns, so you can order by any desired column using it's number.

Answer 12

We can use ORDER BY clause with UNION result, after researching for a long time, I finally came to a solution.

In MySQL if you use parenthesis then scope of query is limited to parenthesis, if you want to sort the UNION result data coming from two or more complex queries use all SELECT and UNION statement in one line and in ORDER BY clause use the name of column you want to sort.

EX: SELECT * FROM customers UNION SELECT * FROM users ORDER BY name DESC

If you are using JOIN in SELECT queries than use only column name only not with the table variable name

EX: SELECT c.name,c.email FROM customers as c JOIN orders as o ON c.id=o.id UNION SELECT u.name,u.email FROM users as u JOIN inventory as i ON u.id=i.id ORDER BY name DESC

Answer 13

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 14

My favorite solution is to create a CTE

WITH cte as (
(SELECT * FROM table1)

UNION ALL 

(SELECT * FROM table2)
)
SELECT * 
FROM cte 
ORDER BY col1