MySQL Sorting on two columns with UNION

June 30, 2022

SO I have a couple of tables that I’d like to combine into one list. I have limited knowledge of what I can do with MySQL and already hit the fact that UNION needs to have the same amount of columns…

One table has data like the following:

batch_no
1
2
3
4
5
6
9
10
12

The other has

batch_no	subbatch_no
7	1
7	2
7	3
8	1
8	2
11	1
11	2

I basically want to be able to have a output that displays like this:

batch_no
1
2
3
4
5
6
7-1
7-2
7-3
8-1
8-2
9
10
11-1
11-2
12

I’ve had various attempts but the following is the best I came up with but obviously is sorting incorrectly…

SELECT batch_no FROM batch 
UNION
SELECT CONCAT(batch_no,'-',subbatch_no) FROM subbatch 
ORDER BY batch_no DESC

With this the order is seemingly being done as if it were text because I have put the hyphen in, stumped as to how to do this… Any help or advice would be very appreciated. Thank you.

>Solution :

Do the ordering on the original union, adding an extra subbatch_no column to the batch subquery. Then order these together by two columns in the main query, where you can also concatenate them with the - separator.

SELECT CONCAT_WS('-', batch_no, subbatch_no) AS combined_batch_no
FROM (
    SELECT batch_no, NULL AS subbatch_no
    FROM batch
    UNION
    SELECT batch_no, subbatch_no
    FROM subbatch
) AS sub1 
ORDER by batch_no, subbatch_no