Click here to Skip to main content
15,845,681 members
Please Sign up or sign in to vote.
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
Updated 28-Mar-23 10:59am
v2
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

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
 
Share this answer
 
v3
Comments
LB2371 28-Mar-23 10:39am    
Thank you very much for your answer. I'm new to MySQL and I need to learn a lot about it (even if I don't use it very often in my apps).
Just an elucidation: implementing your second query (without UNION), how can I set "ORDER BY DateTime DESC" when Category is not "C4", and "ORDER BY Expiration ASC" when Category is "C4"?
P.S.: I don't use reserved words for column names, they're not in english actually.
CHill60 28-Mar-23 11:57am    
When the category is C4 the Expiration date is used instead of [datetime] in the result set - I'll update my solution - sorry I should have added the ORDER by into that example - it was the main point of your question after all! My bad.
LB2371 28-Mar-23 12:35pm    
MySQL version allows "ORDER BY 4": it works. But in my case, implementing your second query, I need to use "ORDER BY DateTime DESC, Expiration ASC".
CHill60 29-Mar-23 8:44am    
Sorry again - if you use ORDER BY DateTime DESC, Expiration ASC then it will order first by DateTime and then by Expiration where there are multiple entries for any value of DateTime. I've updated my solution with an alternative technique that allows any date to be ordered in a different order depending on category. It would be used with the output of my suggested amendment
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.
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900