SQL Query - Using Order by in Union

Using group/order by with union clause in sql query

A union query may only have one order by clause.

If you are satisfied with ordering the whole resultset, you can remove all order by clauses and just keep the very last one, at the end of the query. It applies to the entire dataset that union generates.

Note that your UNIONs are equivalent to UNION ALLs - because the client name is different in each member - and should be phrased as such.

If, on the other hand, you want to order reach sub-result, then this is different. Basically you need a flag in each member, that can then be used to identify each group. The client name might be a good pick, so:

order by client_name, client_id

SQL - How to Order By in UNION query

Put a UNION ALL in a derived table. To keep duplicate elimination, do select distinct and also add a NOT EXISTS to second select to avoid returning same person twice if found in both tables:

select name, surname
from
(
    select distinct name, surname, 1 as tno
    from table1
    union all
    select distinct name, surname, 2 as tno
    from table2 t2
    where not exists (select * from table1 t1
                      where t2.name = t1.name
                        and t2.surname = t1.surname)
) dt
order by tno, surname, name

Using union and order by clause in mysql

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

Mysql multiple ORDER BY with UNION

Try this

SELECT n.nid, max(na.gid) as mid,fav.field_date_posted_value, UNIX_TIMESTAMP(fav.field_date_posted_value) as pdate, 1 as ob FROM `node` as n
JOIN nodeaccess AS na ON na.nid = n.nid
LEFT JOIN field_data_field_date_posted AS fav ON fav.entity_id = n.nid
WHERE (na.gid IN(10,11) AND (n.status = '1') AND (n.type IN  ('article','blog', 'events', 'media', 'press_releases', 'expert_speak', 'feature', 'case_study', 'news', 'the_igtb_series', 'trend', 'white_paper')) )
GROUP BY n.nid 


UNION ALL

SELECT n.nid, max(na.gid) as mid,fav.field_date_posted_value, UNIX_TIMESTAMP(fav.field_date_posted_value) as pdate, 2 as ob FROM `node` as n
JOIN nodeaccess AS na ON na.nid = n.nid
LEFT JOIN field_data_field_date_posted AS fav ON fav.entity_id = n.nid
WHERE (na.gid IN(2) AND (n.status = '1') AND (n.type IN  ('article','blog', 'events', 'media', 'press_releases', 'expert_speak', 'feature', 'case_study', 'news', 'the_igtb_series', 'trend', 'white_paper')) )
GROUP BY n.nid  

ORDER BY ob ASC, pdate DESC

Fixed the order by date only base or group

ORDER BY with UNION clause

You would need to put the union in a subquery, and order in the outer query. Also, the subquery needs to return the column that you want to use for sorting.

SELECT *
FROM (
    SELECT bo.id, bo.date, bo.status, bo2.u_registration_date
        FROM BO
        JOIN BO2 ON BO2.bo2stamp = BO.bostamp
        WHERE BO.date BETWEEN @inidate AND @findate AND BO.tabela1 IN ('INSTALED','CANCELED')
    UNION
        SELECT bo.id, bo.date, bo.status, bo2.u_registration_date
        FROM BO
        JOIN BO2 ON BO2.bo2stamp = BO.bostamp
        WHERE BO.date < @inidate AND BO.tabela1 NOT IN ('INSTALED','CANCELED')
) t
ORDER BY u_registration_date

Note that using UNION here does not make a lot of sense. The two members only differ by their date filter, so functionally your query is equivalent to just:

SELECT bo.id, bo.date, bo.status, bo2.u_registration_date
FROM BO
JOIN BO2 ON BO2.bo2stamp = BO.bostamp
WHERE BO.date < @findate AND BO.tabela1 NOT IN ('INSTALED','CANCELED')
ORDER BY bo2.u_registration_date

access-SQL-Query - Using Order By in UNION ALL

Try this:

SELECT table1.name, table1.age FROM table1
UNION ALL
SELECT table2.name, table2.age FROM table2
UNION ALL
SELECT table3.name, table3.age FROM table3 
ORDER BY 2;

You are ordering the results from your sub-queries, then joining these together into an unordered list. You need to move the ORDER BY to the end of the query.

As Damien says, you need to ORDER BY at the end of your query, otherwise you can't guarantee the results will always be what you want. Something like this should do the job:

SELECT name, age FROM (
    SELECT 1 AS table_order, table1.name, table1.age FROM table1
    UNION ALL
    SELECT 2 AS table_order, table2.name, table2.age FROM table2
    UNION ALL
    SELECT 3 AS table_order, table3.name, table3.age FROM table3 
) x
ORDER BY table_order, age;

SQL Query - Using Order By in UNION

Sometimes you need to have the ORDER BY in each of the sections that need to be combined with UNION.

In this case

SELECT * FROM 
(
  SELECT table1.field1 FROM table1 ORDER BY table1.field1
) DUMMY_ALIAS1

UNION ALL

SELECT * FROM
( 
  SELECT table2.field1 FROM table2 ORDER BY table2.field1
) DUMMY_ALIAS2

---

Related Topics