Using union and order by clause in mysql

Question

I want to use order with union in mysql query. I retrieve different types of records based on different criteria from a table based on the distance to search on my site. The first select query returns data related to the exact location search. The second select query returns data related to a distance within 5 km of the search location. The third select query returns data related to the distance within 5-15 km from the search location.

Then I m, using the union, combines all the results and is shown on the page with the paging. Under the corresponding heading "Exact search results" , "Results within 5 km" , etc.

Now I want to sort the 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 the results. But I want it to be sorted under each heading.

Answer 1

You can do this by adding a pseudo-column called rank to each element that you can sort first before sorting by other criteria, for example:

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

For this you can use subqueries:

 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

A join request can contain only one main ORDER BY , IIRC ORDER BY . To get this, in each query making up a higher UNION query, add a field that will be the only field you sort for UNION ORDER BY .

For example, 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 

This union_sort field can be anything you can sort. In this example, it just happens that the first tables are displayed first, the second second, etc.

Answer 5

Remember that merging everyone is a way to add records to a set of records without sorting or merging (as opposed to merging).

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 

Saves individual queries more clearly and allows you to sort by various parameters in each query. However, using the chosen answer method, it can become clearer depending on the complexity and how the data is related, because you conceptualize sorting. It also allows you to return an artificial column to the query program so that it has a context that it can sort or organize.

But this method has the advantage of being fast, not introducing additional variables and simplifying the separation of each request, including sorting. The ability to add a limit is just an added bonus.

And, of course, feel free to turn the union into a union and add sorting for the entire query. Or add an artificial identifier, in which case this method simplifies sorting by various parameters in each request, but otherwise it matches the accepted answer.

Answer 6

I worked on unification and unification.

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

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

Answer 8

This is because you are sorting the entire set of results, you have to sort each part of the union separately, or you can use ORDER BY (something, that is, a subquery interval). THEN clause (something, i.e. id string)

Answer 9

I tried to add order to each of the requests before merging, for example

 (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 did not seem to work. This did not actually order the rows from each selection.

I think you will need to keep the order outside and add the columns in the where clause to the order, something like

 (select * from table where distance=0) union (select * from table where distance>0 and distance<=5) order by distance, add_date 

It can be a little tricky as you want to group the ranges, but I think it should be doable.

Answer 10

When you use the ORDER BY inside a subquery used with UNION mysql optimizes the ORDER BY .

This is because, by default, UNION returns an unordered list, so ORDER BY will not do anything.

Optimization is mentioned in the documentation and says:

To apply ORDER BY or LIMIT to a single SELECT, put the statement in parentheses that enclose 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, using ORDER BY for individual SELECT statements implies nothing in the order in which the rows appear in the final result, since UNION creates an unordered set of rows by default. Consequently, using ORDER BY in this context is usually associated with LIMIT, so it is used to define a subset of the selected rows to retrieve for SELECT, even if this does not necessarily affect the order of these rows in the final UNION result. If ORDER BY appears without a LIMIT in a SELECT, it is optimized because it will still have no effect.

The last sentence is a bit misleading, because it should have an effect. This optimization causes a problem when you are in a situation where you need to place an order inside a subquery.

To prevent MySQL from performing this optimization, you can add a LIMIT clause as follows:

 (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 can add OFFSET to the general query if you want to do something, such as pagination.

It also gives you the added benefit of ORDER BY able to have different columns for each join.