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.
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)
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
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
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
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
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