How to use order by with union all in sql?
SELECT *
FROM
(
SELECT * FROM TABLE_A
UNION ALL
SELECT * FROM TABLE_B
) dum
-- ORDER BY .....
but if you want to have all records from Table_A
on the top of the result list, the you can add user define value which you can use for ordering,
SELECT *
FROM
(
SELECT *, 1 sortby FROM TABLE_A
UNION ALL
SELECT *, 2 sortby FROM TABLE_B
) dum
ORDER BY sortby
Use ORDER BY with UNION ALL
SELECT *
FROM
(
SELECT EventName, EventDate, 1 AS OrderPri,
ROW_NUMBER() OVER(ORDER BY EventDate) AS Row
FROM EventTable
WHERE EventDate > GETDATE()
UNION ALL
SELECT EventName, EventDate, 2 AS OrderPri,
ROW_NUMBER() OVER(ORDER BY EventDate DESC) AS Row
FROM EventTable
WHERE EventDate <= GETDATE()
) AS m
ORDER BY m.OrderPri, m.Row
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
How to ORDER BY one query only in a UNION ALL without subquery
The ORDER BY
doesn't apply to just one SELECT
, it applies to the whole result set, to the whole result after all rows are UNION
ed. So, you need to include columns that will be used for sorting in each SELECT
of the UNION ALL
.
For example, the following is wrong syntax, because ORDER BY
has to be after the last SELECT
of the UNION
.
SELECT CASE
WHEN p.Category = 1
THEN 'Very Active'
WHEN p.Category = 2
THEN 'Active '
ELSE 'Departed'
END AS Matrix
, p.name AS [Name]
FROM #People p
ORDER BY p.Category
UNION ALL
SELECT 'Best Employee' AS Matrix
, 'Alan' AS [Name]
Your second example from the question can be written in a simpler form, without sub-query:
SELECT CASE
WHEN p.Category = 1
THEN 'Very Active'
WHEN p.Category = 2
THEN 'Active '
ELSE 'Departed'
END AS Matrix
, p.name AS [Name]
, p.Category
FROM #People p
UNION ALL
SELECT 'Best Employee' AS Matrix
, 'Alan' AS [Name]
, NULL AS Category --- or '' AS Category
ORDER BY Category;
In any case, the column Category
has to be added in each SELECT
statement of the UNION
. I don't see how you can avoid it.
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;
How to use ORDER BY inside UNION
Something like this should work in MySQL:
SELECT a.*
FROM (
SELECT ... FROM ... ORDER BY ...
) a
UNION ALL
SELECT b.*
FROM (
SELECT ... FROM ... ORDER BY ...
) b
to return rows in an order we'd like them returned. i.e. MySQL seems to honor the ORDER BY
clauses inside the inline views.
But, without an ORDER BY
clause on the outermost query, the order that the rows are returned is not guaranteed.
If we need the rows returned in a particular sequence, we can include an ORDER BY
on the outermost query. In a lot of use cases, we can just use an ORDER BY
on the outermost query to satisfy the results.
But when we have a use case where we need all the rows from the first query returned before all the rows from the second query, one option is to include an extra discriminator column in each of the queries. For example, add ,'a' AS src
in the first query, ,'b' AS src
to the second query.
Then the outermost query could include ORDER BY src, name
, to guarantee the sequence of the results.
FOLLOWUP
In your original query, the ORDER BY
in your queries is discarded by the optimizer; since there is no ORDER BY
applied to the outer query, MySQL is free to return the rows in whatever order it wants.
The "trick" in query in my answer (above) is dependent on behavior that may be specific to some versions of MySQL.
Test case:
populate tables
CREATE TABLE foo2 (id INT PRIMARY KEY, role VARCHAR(20)) ENGINE=InnoDB;
CREATE TABLE foo3 (id INT PRIMARY KEY, role VARCHAR(20)) ENGINE=InnoDB;
INSERT INTO foo2 (id, role) VALUES
(1,'sam'),(2,'frodo'),(3,'aragorn'),(4,'pippin'),(5,'gandalf');
INSERT INTO foo3 (id, role) VALUES
(1,'gimli'),(2,'boromir'),(3,'elron'),(4,'merry'),(5,'legolas');
query
SELECT a.*
FROM ( SELECT s.id, s.role
FROM foo2 s
ORDER BY s.role
) a
UNION ALL
SELECT b.*
FROM ( SELECT t.id, t.role
FROM foo3 t
ORDER BY t.role
) b
resultset returned
id role
------ ---------
3 aragorn
2 frodo
5 gandalf
4 pippin
1 sam
2 boromir
3 elron
1 gimli
5 legolas
4 merry
The rows from foo2
are returned "in order", followed by the rows from foo3
, again, "in order".
Note (again) that this behavior is NOT guaranteed. (The behavior we observer is a side effect of how MySQL processes inline views (derived tables). This behavior may be different in versions after 5.5.)
If you need the rows returned in a particular order, then specify an ORDER BY
clause for the outermost query. And that ordering will apply to the entire resultset.
As I mentioned earlier, if I needed the rows from the first query first, followed by the second query, I would include a "discriminator" column in each query, and then include the "discriminator" column in the ORDER BY clause. I would also do away with the inline views, and do something like this:
SELECT s.id, s.role, 's' AS src
FROM foo2 s
UNION ALL
SELECT t.id, t.role, 't' AS src
FROM foo3 t
ORDER BY src, role
Combining ORDER BY AND UNION in SQL Server
Put your order by
and top
statements into sub-queries:
select first.Id, first.Name
from (
select top 1 *
from Locations
order by Id) first
union all
select last.Id, last.Name
from (
select top 1 *
from Locations
order by Id desc) last
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
Related Topics
Query to Find Nᵗʰ Max Value of a Column
Remove Duplicates in a Django Query
Join to Only the "Latest" Record with T-Sql
SQL Server 2005 Using Dateadd to Add a Day to a Date
Add Row Number to This T-SQL Query
Pros and Cons of Autoincrement Keys on "Every Table"
Clean Way to Use Postgresql Window Functions in Django Orm
Error: Column of Relation Does Not Exist Postgresql ,Unable to Run Insert Query
SQL Injections with Prepared Statements
SQL Server Equivalent of MySQL's Now()
How to Select Rows for a Specific Date, Ignoring Time in SQL Server
Split Varchar into Separate Columns in Oracle
What Is the Data Type for Unix_Timestamp (Mysql)
Prevent Insert If Condition Is Met