How can i use ORDER BY with UNION ALL in a sql query?

Question

I have problems with ordering the results of a query that joins 4 different tables with UNION ALL operator.

What I have tried is something like this:

SELECT * FROM (
SELECT charge, payment, date FROM table_1
UNION ALL
SELECT charge, payment, date FROM table_2
UNION ALL
SELECT charge, payment, date FROM table_3
UNION ALL
SELECT charge, payment, date FROM table_4
) balance
ORDER BY date ASC;

when I order by certain parameters it works fine, but there are others that return the data ordered by sections instead of as a whole

When I order by date it works as it should, like this:

charge  |  payment  |  date  
------------------------------
   10   |    x      | 2018-03-23
   2    |    x      | 2018-10-20
   8    |    x      | 2019-03-06
   30   |    x      | 2019-05-10
   6    |    x      | 2019-11-10
   15   |    x      | 2020-07-16
   11   |    x      | 2020-09-20

but when I order by another parameter, in this case "chage", it orders like this:

charge  |  payment  |  date  
------------------------------
   10   |    x      | 2018-03-23
   6    |    x      | 2019-11-10
   30   |    x      | 2019-05-10
   8    |    x      | 2019-03-06
   15   |    x      | 2020-07-16
   11   |    x      | 2020-09-20
   6    |    x      | 2019-11-10

Sorry if I don't explain myself well, English is not my first language.

What you're doing should be correct. That aside, `date` is a reserved word - if you change the column name, does that fix things? — Clockwork-Muse, Jan 28 '22 at 03:07
Your query [should work as expected](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=49ecd9288d82fe015fed2ffbb55e6e63). Are you sure you're just doing `ORDER BY date ASC`? Just the `date` column? — FanoFN, Jan 28 '22 at 03:10
I noticed that when I order by date, in fact, I have no problem, but when I order by the other parameters. I used date because I thought it was the clearest to explain :/ — estebanovic, Jan 28 '22 at 03:13
I removed the inconsistent database tags. Please include a specific database tag only, — The Impaler, Jan 28 '22 at 03:34

score 4 · Answer 1 · answered Jan 28 '22 at 03:11

4

Actually, you don't even need the subquery, the following should work:

SELECT charge, payment, date FROM table_1
UNION ALL
SELECT charge, payment, date FROM table_2
UNION ALL
SELECT charge, payment, date FROM table_3
UNION ALL
SELECT charge, payment, date FROM table_4
ORDER BY date;

The final ORDER BY clause applies to the entire union query.

answered Jan 28 '22 at 03:11

Tim Biegeleisen

502,043
27
286
360

yes, you are right, anyway i still have the problem. I really don't know what it could be, because when sorting by date it works well but with other data it doesn't. – estebanovic Jan 28 '22 at 03:18
If my answer doesn't give you the output you expect, then what output do you want to see here? – Tim Biegeleisen Jan 28 '22 at 03:19
Please _edit_ your question and put minimal sample data there, as _text_, not as images. – Tim Biegeleisen Jan 28 '22 at 03:25
@estebanovic I don't see any "ordering by sections". What I do see is that "abono" is a string so it is ordered as a string not a number – Martin Smith Jan 28 '22 at 03:33
@MartinSmith ok i changed it – estebanovic Jan 28 '22 at 03:36
You changed the text in your question to something not shown in your actual screenshot you posted above – Martin Smith Jan 28 '22 at 03:37
1

@MartinSmith you're right. the fields are integers, but following the old code where in the tables that there were no values I changed it to an empty string, that made them compare as strings. thanks and sorry if i bothered you with my mistake. – estebanovic Jan 28 '22 at 03:42
@estebanovic If that's the issue here, that you can try ordering using `CAST(some_field AS int)`, where `some_field` is a text column however containing numbers. Best practice is that if you need to use casts all over the place, the `some_field` column should probably be numeric, rather than text. – Tim Biegeleisen Jan 28 '22 at 03:44
@TimBiegeleisen Yes, change the empty string to a zero in the default cases and now it works as it should. – estebanovic Jan 28 '22 at 03:46

score 0 · Answer 2 · answered Jan 28 '22 at 03:14

When you apply ORDER BY at the end of UNION ALL, it is applied to whole resultset.

The logical order of processing a sql query is given below. Reference. So, the ORDER BY is applied after UNION ALL application.

FROM
ON
JOIN
WHERE
GROUP BY
WITH CUBE or WITH ROLLUP
HAVING
SELECT
DISTINCT
ORDER BY
TOP

DECLARE @table1 table (charge  char(1),  payment char(1),  dateval date)
DECLARE @table2 table (charge  char(1),  payment char(1),  dateval date)
DECLARE @table3 table (charge  char(1),  payment char(1),  dateval date)
DECLARE @table4 table (charge  char(1),  payment char(1),  dateval date)

insert into @table1 values('x','x','2018-03-23')
insert into @table2 values('x','x','2019-03-06')
insert into @table3 values('x','x','2019-11-10')
insert into @table4 values('x','x','2020-09-20')

Select * from @table1
UNION ALL
Select * from @table2
union all
Select * from @table3
union all
Select * from @table4
order by dateval

charge	payment	dateval
x	x	2018-03-23
x	x	2019-03-06
x	x	2019-11-10
x	x	2020-09-20

How can i use ORDER BY with UNION ALL in a sql query?

2 Answers2