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.
This question is related to
mysql
sql
sql-order-by
union
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
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.
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.
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.
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.
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 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.
(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
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
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.
Source: Stackoverflow.com