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

User · 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

User · Answer

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

User · Answer

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 gt 0 and distance lt  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 gt 0 and distance lt  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · 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 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

User · Answer

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

User · Answer

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      lt      gt      order by col3     limit by 200   a union all select   from       select cola  colb     from table b      lt      gt      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

User · Answer

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

[mysql] Using union and order by clause in mysql

Examples related to mysql

Examples related to sql

Examples related to sql-order-by

Examples related to union