MySQL Sorting on two columns with UNION-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

it is actually quite easy sorting apha numrical qith your data

SELECT CAST(batch_no as char) batchno FROM batch 
UNION 
SELECT CONCAT(batch_no,'-',subbatch_no) FROM subbatch 
ORDER BY batchno 0 ASC

| batchno |
| :------ |
| 1       |
| 2       |
| 3       |
| 4       |
| 5       |
| 6       |
| 7-1     |
| 7-2     |
| 7-3     |
| 8-1     |
| 8-2     |
| 9       |
| 10      |
| 11-1    |
| 11-2    |
| 12      |

db<>fiddle here

CodePudding user response：

select CONCAT_WS('-',batch_no,subbatch_no) from (
  SELECT batch_no,null subbatch_no FROM batch 
  UNION ALL
  SELECT batch_no,subbatch_no FROM subbatch 
) A
ORDER BY batch_no,subbatch_no

CodePudding user response：

Try with the following:

WITH cte AS (
    SELECT batch_no, NULL as subbatch_no FROM tab1
    UNION ALL
    SELECT * FROM tab2
)
SELECT CONCAT_WS('-' , batch_no, subbatch_no) AS batch_no
FROM cte
ORDER BY cte.batch_no, cte.subbatch_no

Check the demo here.