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 UNION
s are equivalent to UNION ALL
s - 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
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
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 clause with Union in Sql Server
You need to use a sub-query with CASE
in ORDER BY
clause like this:
SELECT * FROM
(
Select 0 PartyId, 'All' Name
Union
select PartyId, Name
from PartyMst
) tbl
ORDER BY CASE WHEN PartyId = 0 THEN 0 ELSE 1 END
,Name
Output:
PARTYID | NAME |
---|---|
0 | All |
2 | AAKASH & CO. |
3 | SHAH & CO. |
1 | SHIV ELECTRONICS |
SQL: How to order a union alphabetically?
Ironically, in many databases, your query would return the values in alphabetical order. The sorting is done during the duplicate removal for union
.
Of course, you can't depend on that. So, you should include an order by
clause:
select name
from Reviewer
union
select model
from Car
order by name;
The order by
applies to the complete union
. The column name comes from the first subquery.
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.
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
Related Topics
Can a Foreign Key Be Null And/Or Duplicate
Why No Windowed Functions in Where Clauses
Concatenate Multiple Result Rows of One Column into One, Group by Another Column
Calculating Difference Between Two Timestamps in Oracle in Milliseconds
How to Check If a Column Is Empty or Null in MySQL
Is There a Group_Concat Function in Ms-Access
Using 'Case Expression Column' in Where Clause
Multiplication Aggregate Operator in Sql
Foreign Key Referring to Primary Keys Across Multiple Tables
SQL Query - Using Order by in Union
Difference Between Cte and Subquery