How to use mysql SELECT UNION with different ORDER by statements?

Question

0.00/5 (No votes)

See more:

I have the following MySQL query:

SQL

(SELECT
Url, Ref, Text, DateTime, 'C1' AS Source
FROM Tbl
WHERE Category = 'C1')

UNION

(SELECT
Url, Ref, Text, DateTime, 'C2' AS Source
FROM Tbl
WHERE Category = 'C2')

UNION

(SELECT
Url, Ref, Text, DateTime, 'C3' AS Source
FROM Tbl
WHERE Category = 'C3')

ORDER BY DateTime DESC

Now I need to merge another query, that has a different "ORDER BY":

SQL

UNION

(SELECT
Url, Ref, Text, Expiration, 'C4' AS Source
FROM Tbl
WHERE Category = 'C4' AND Expiration >= CURDATE()
ORDER BY Expiration ASC)

I've tried to add it as above, but it doesn't work.

What's wrong?

What I have tried:

SQL

UNION

(SELECT
Url, Ref, Text, Expiration, 'C4' AS Source
FROM Tbl
WHERE Category = 'C4' AND Expiration >= CURDATE()
ORDER BY Expiration ASC)

Posted 24-Mar-23 9:56am

LB2371

Updated 28-Mar-23 9:59am

v2

Add a Solution

Comments

Member 15627495 25-Mar-23 5:36am

'Datetime' and 'expiration' , may need an ALIAS as common name to merge in the query result.
DateTime as 'DATE' ,
and expiration as 'DATE'

LB2371 25-Mar-23 5:49am

I've tried this:

(SELECT
Url, Ref, Text, DateTime AS DATE, 'C1' AS Source
FROM Tbl
WHERE Category = 'C1')

UNION

(SELECT
Url, Ref, Text, DateTime AS DATE, 'C2' AS Source
FROM Tbl
WHERE Category = 'C2')

UNION

(SELECT
Url, Ref, Text, DateTime AS DATE, 'C3' AS Source
FROM Tbl
WHERE Category = 'C3')

ORDER BY DATE DESC

UNION

(SELECT
Url, Ref, Text, Expiration AS DATE, 'C4' AS Source
FROM Tbl
WHERE Category = 'C4' AND Expiration >= CURDATE()
ORDER BY DATE ASC)

But it doesn't work. How would you rewrite the query?

Member 15627495 25-Mar-23 5:56am

you use (...) to isolate your queries but the UNION do it already too ( it's just syntax, not a fail case )

do you have an error message as feedback ?

surround by ' ' the alias , because of requirements
as 'date'

Member 15627495 25-Mar-23 6:03am

look at this answers topic :

https://stackoverflow.com/questions/4715820/how-to-order-by-with-union-in-sql

2 solutions

Solution 2

This is my own solution:

SQL

SELECT Url, Ref, Text, IF (Category <> 'C4', DateTime, Expiration), Category
FROM Tbl
WHERE Category <> 'C4'
OR (Category = 'C4' AND Expiration >= curdate())
ORDER BY DateTime DESC, Expiration ASC

Any observations/suggestions would be much appreciated.

Posted 28-Mar-23 9:59am

LB2371

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2023-03-28T03:54:00

If you look at your first query the ORDER BY is applied to the entire result set.
You cannot use ORDER BY in sub-queries the way you are attempting, unless you treat the whole thing like a proper sub-query that you are querying from e.g.

SQL

UNION
SELECT Url, Ref, Text, DATE, Source FROM
(SELECT
Url, Ref, Text, Expiration AS DATE, 'C4' AS Source
FROM Tbl
WHERE Category = 'C4' AND Expiration >= CURDATE()
ORDER BY DATE ASC) AS TblC4

You don't even need that column alias DATE because the result set from the union will take it's column names from the very first select in the UNION trail.

You don't need the individual sort order because you are sorting everything on the datetime provided (by the way - it's bad practice to use reserved words for column names)

You also don't need to use UNION at all. Something like this is far easier (and more performant)

SQL

SELECT
Url, Ref, [Text]
, case when category <> 'C4' then [DateTime] else [Expiration] END AS [datecolumn]
, Category AS Source
FROM #Tbl
WHERE category <> 'C4'
or (Category = 'C4' AND Expiration >= curdate())

Finally, "it doesn't work" is the least helpful bit of information anyone can give. Be specfic - quote the actual runtime error message or syntax error, or describe why the results were different to those you expected. You will get more accurate assistance far quicker if you do.

EDIT: I missed off the very point of the question - ie ordering the data in my final example. Apologies...

When the category is C4 the Expiration date is used instead of [datetime] in the result set so you should be able to use

SQL

ORDER BY 4

I.e. that reads as "order by column 4 of the results". But if your version of MySQL does not allow that then you can try this

SQL

SELECT Url, Ref, [Text], [datecolumn], [source] FROM
(
     SELECT
     Url, Ref, [Text]
     , case when category <> 'C4' then [DateTime] else [Expiration] END AS [datecolumn]
     , Category AS Source
     FROM #Tbl
     WHERE category <> 'C4'
     or (Category = 'C4' AND Expiration >= curdate())
) tblR 
ORDER BY [datecolumn]

EDIT: A technique for sorting in a different order depending on category - apologies this is in SQL Server not MySql so the syntax may not be fully accurate

I used this sample data

SQL

declare @demo table (keydate date, cat int);
insert into @demo (keydate, cat) values
('2023-01-01', 1),
('2023-02-01', 1),
('2023-03-01', 1),
('2023-04-01', 1),
('2023-01-01', 4),
('2023-02-01', 4),
('2023-03-01', 4),
('2023-04-01', 4);

Then this will sort on that date descending for cat = 4 and ascending for any other value of cat

SQL

declare @mindate date = (select MIN(keydate) from @demo);
select	 keydate
		,cat
		, case when cat = 4 then DATEDIFF(d, keydate, @mindate) else DATEDIFF(d, @mindate, keydate) end
from @demo
order by 3

Results

keydate	cat	(No column name)
2023-04-01	4	-90
2023-03-01	4	-59
2023-02-01	4	-31
2023-01-01	4	0
2023-01-01	1	0
2023-02-01	1	31
2023-03-01	1	59
2023-04-01	1	90