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.
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
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
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
(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
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.
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
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
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.
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)
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.
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
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;
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.
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
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.
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.
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
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
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