MySQL Sorting on two columns with UNION

Question

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.

UNION remove duplicates from each table if any

Kadet
– Kadet

2022-06-29 22:38:56 +00:00
Commented Jun 29, 2022 at 22:38 — Kadet
– Kadet, Commented Jun 29, 2022 at 22:38

Barmar · Accepted Answer · 2022-06-29 22:32:14Z

1

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

answered Jun 29, 2022 at 22:32

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

nbk · Accepted Answer · 2022-06-29 22:39:01Z

1

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

answered Jun 29, 2022 at 22:39

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Comments

Kadet · Accepted Answer · 2022-06-29 22:30:36Z

0

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

edited Jun 29, 2022 at 22:30

answered Jun 29, 2022 at 22:26

Kadet

1,4195 silver badges11 bronze badges

2 Comments

nbk Over a year ago

that will not give the result

Barmar Over a year ago

This only applies ORDER BY to the second query. It doesn't order the result.

lemon · Accepted Answer · 2022-06-29 22:31:14Z

0

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.

answered Jun 29, 2022 at 22:31

lemon

15.6k6 gold badges23 silver badges42 bronze badges

Collectives™ on Stack Overflow

MySQL Sorting on two columns with UNION

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related