How to order by with union in SQL

Question

Is it possible to order when the data is come from many select and union it together  Such as Select id name age From Student Where age  lt  15 Union Select id name age From Student Where Name like  quot  a  quot   How can I order this query by name  I tried this Select id name age From Student Where age  lt  15 or name like  quot  a  quot  Order by name  But that does not work

User · Answer

Add a column to the query which can sub identify the data to sort on that.

In the below example I use a Common Table Expression with the selects you showed which places them in specific groups in the CTE, and then do a union off of both of those groups into AllStudents.

The final select will then sort AllStudents by the SortIndex column first and then by the name such as:

WITH Juveniles as
(
      Select 1 as [SortIndex], id,name,age From Student
      Where age < 15
),

AStudents as
(
      Select 2 as [SortIndex], id,name,age From Student
      Where Name like "%a%" 
),

AllStudents as
(
      select * from Juveniles
      union 
      select * from AStudents
)

select * from AllStudents
sort by [SortIndex], name;

To summarize, it will get all the students which will be sorted by group first, and subsorted by the name within the group after that.

User · Answer

Just write  Select id name age From Student Where age  lt  15 Union Select id name age From Student Where Name like   a   Order by name   the order by is applied to the complete resultset

User · Answer

Why not use TOP X  SELECT pass1   FROM    SELECT TOP 2000000 tblA ID  tblA CustomerName    FROM TABLE A AS tblA ORDER BY 2  AS pass1 UNION ALL  SELECT pass2   FROM     SELECT TOP 2000000 tblB ID  tblB CustomerName     FROM TABLE B AS tblB ORDER BY 2  AS pass2  The TOP 2000000 is an arbitrary number  that is big enough to capture all of the data  Adjust as per your requirements

User · Answer

In order to make the sort apply to only the first statement in the UNION  you can put it in a subselect with UNION ALL  both of these appear to be necessary in Oracle    Select id name age FROM         Select id name age  From Student  Where age  lt  15  Order by name   UNION ALL Select id name age From Student Where Name like   a     Or  addressing Nicholas Carey s comment  you can guarantee the top SELECT is ordered and results appear above the bottom SELECT like this   Select id name age  1 as rowOrder From Student Where age  lt  15 UNION Select id name age  2 as rowOrder From Student Where Name like   a   Order by rowOrder  name

User · Answer

As other answers stated    Order by  after LAST Union should apply to both datasets joined by union   I was having two data sets but using different tables but same columns   Order by  after LAST Union didn t still worked  Using ALIAS for column used in  order by  did the trick   Select Name  Address for Employee  Union Select Customer Name  Address from Customer order by customer name    --Won t work   So solution is use Alias  User Name     Select Name as User Name  Address for Employee  Union Select Customer Name as User Name  Address from Customer order by User Name

User · Answer

If I want the sort to be applied to only one of the UNION if use Union all   Select id name age From Student Where age  lt  15 Union all Select id name age From    Select id name age From Student Where Name like   a   Order by name

User · Answer

Can use this   Select id name age From Student Where age  lt  15 Union ALL SELECT   FROM  Select id name age From Student Where Name like   a

User · Answer

Select id name age from      Select id name age    From Student    Where age  lt  15   Union    Select id name age    From Student    Where Name like   a     results order by name

User · Answer

To apply an ORDER BY or LIMIT clause to an individual SELECT  parenthesize the SELECT and place the clause inside the parentheses   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

User · Answer

Order By is applied after union  so just add an order by clause at the end of the statements   Select id name age From Student Where age  lt  15 Union Select id name age From Student Where Name like   a   Order By name

User · Answer

Both other answers are correct  but I thought it worth noting that the place where I got stuck was not realizing that you ll need order by the alias and make sure that the alias is the same for both the selects    so select  foo  union select item as  foo  from myTable order by  foo   notice that I m using single quotes in the first select but backticks for the others  That will get you the sorting you need

[sql] How to order by with union in SQL?

Examples related to sql

Examples related to sql-order-by

Examples related to union