Order by with UNION statement

Question

I have a little issue with the ORDER BY statement.

Here is my query (sorry I know is a little bit long, but I alredy cut a lot of the code)

(SELECT AssAttrezzi.ID, AssBombole.matricola, CONCAT(AssBombole.matricola, ' - ', Bombole.nome) AS Descrizione 
 FROM AssAttrezzi INNER JOIN 
      AssBombole 
      ON AssBombole.matricola = AssAttrezzi.attrezzoID INNER JOIN 
      Bombole 
      ON Bombole.ID = AssBombole.tipoBombolaID 
 WHERE AssAttrezzi.cantiereID=1
) UNION
(SELECT AssAttrezzi.ID, Saldatrici.matricola,CONCAT(Saldatrici.matricola, ' - ', Saldatrici.nome) AS Descrizione 
 FROM AssAttrezzi INNER JOIN 
      Saldatrici 
      ON Saldatrici.matricola = AssAttrezzi.attrezzoID 
 WHERE AssAttrezzi.cantiereID = 1
) UNION
(SELECT AssAttrezzi.ID, AssBanchiGlad.idAtrezzo, CONCAT(AssBanchiGlad.idAtrezzo, ' - ', BanchiGladiator.nome) AS Descrizione 
 FROM AssAttrezzi INNER JOIN 
      AssBanchiGlad 
      ON AssBanchiGlad.idAtrezzo = AssAttrezzi.attrezzoID INNER JOIN
      BanchiGladiator 
      ON BanchiGladiator.ID = AssBanchiGlad.bancoID 
 WHERE AssAttrezzi.cantiereID = 1
) ORDER BY LEN(matricola), matricola

The problem is when I put ORDER BY at the end of each SELECT It gives me error:

Incorrect syntax near UNION

But when I try to put the ORDER BY at the end of all it gives me another error:

If the ORDER BY statement includes the UNION, INTERSECT or EXCEPT operator, the elements of the instruction must be specified in the selection list.

What? I would like to order by *.matricola basically matricola for me is the important one.

Does this answer your question? [How to use order by with union all in sql?](https://stackoverflow.com/questions/15470191/how-to-use-order-by-with-union-all-in-sql) — SMor, Jan 22 '20 at 14:43
I looked at all the question in this forum before asking... but its seems like this ORDER BY doesn't want to work... — ChangeWorld, Jan 22 '20 at 14:49
Show your `ORDER BY` clause. I guess it contains columns which are not in `SELECT` statement. — Max Zolotenko, Jan 22 '20 at 14:52
@МаксимЗолотенко I've edited the question, thank you, ehm if I use only ORDER BY matricola, it works but it doesn't order them? What? — ChangeWorld, Jan 22 '20 at 14:53
You can't do ordering by `matricola` because it doesn't contain in `SELECT` statements. Try to add `matricola` to `SELECT` lists. `LEN(matricola)` as well. — Max Zolotenko, Jan 22 '20 at 14:55
@МаксимЗолотенко could you make me and example please? — ChangeWorld, Jan 22 '20 at 15:00

Yogesh Sharma · Answer 1 · 2020-01-22T17:48:24.193

You can use custom sort :

SELECT . . . , 1 AS ID
UNION 
SELECT . . . , 2 
UNION
SELECT . . . , 3 
ORDER BY ID;

You can't add separate order by clause when you use UNION/UNION ALL

EDIT :

SELECT AssAttrezzi.ID, AssBombole.matricola, 
       CONCAT(AssBombole.matricola, ' - ', Bombole.nome) AS Descrizione,
       LEN(matricola) AS SortLen 
FROM AssAttrezzi INNER JOIN 
     AssBombole 
     ON AssBombole.matricola = AssAttrezzi.attrezzoID INNER JOIN 
     Bombole 
     ON Bombole.ID = AssBombole.tipoBombolaID 
WHERE AssAttrezzi.cantiereID = 1 
UNION
SELECT AssAttrezzi.ID, Saldatrici.matricola,
       CONCAT(Saldatrici.matricola, ' - ', Saldatrici.nome) AS Descrizione,
       LEN(matricola) 
FROM AssAttrezzi INNER JOIN 
     Saldatrici 
     ON Saldatrici.matricola = AssAttrezzi.attrezzoID 
WHERE AssAttrezzi.cantiereID = 1 
UNION
SELECT AssAttrezzi.ID, AssBanchiGlad.idAtrezzo, 
       CONCAT(AssBanchiGlad.idAtrezzo, ' - ', BanchiGladiator.nome) AS Descrizione, 
       LEN(matricola) 
FROM AssAttrezzi INNER JOIN 
     AssBanchiGlad 
     ON AssBanchiGlad.idAtrezzo = AssAttrezzi.attrezzoID INNER JOIN
     BanchiGladiator 
     ON BanchiGladiator.ID = AssBanchiGlad.bancoID 
WHERE AssAttrezzi.cantiereID = 1
ORDER BY SortLen, matricola;

Could you copy, paste my code into this one? Because I don't get it. — ChangeWorld, Jan 22 '20 at 14:56

score 1 · Accepted Answer · answered Jan 22 '20 at 15:10

Try this:

(
    SELECT  AssAttrezzi.ID,
            AssBombole.matricola,
            CONCAT(AssBombole.matricola, ' - ', Bombole.nome)   AS  Descrizione,
            LEN(AssBombole.matricola)                           AS  matricola_len
    FROM    AssAttrezzi
    INNER JOIN AssBombole ON AssBombole.matricola = AssAttrezzi.attrezzoID
    INNER JOIN Bombole ON Bombole.ID = AssBombole.tipoBombolaID 
    WHERE   AssAttrezzi.cantiereID=1
)
UNION
(
    SELECT  AssAttrezzi.ID,
            Saldatrici.matricola,
            CONCAT(Saldatrici.matricola, ' - ', Saldatrici.nome)    AS  Descrizione,
            LEN(Saldatrici.matricola)                               AS  matricola_len
    FROM    AssAttrezzi
    INNER JOIN Saldatrici ON Saldatrici.matricola = AssAttrezzi.attrezzoID 
    WHERE   AssAttrezzi.cantiereID = 1
)
UNION
(
    SELECT  AssAttrezzi.ID,
            AssBanchiGlad.idAtrezzo,
            CONCAT(AssBanchiGlad.idAtrezzo, ' - ', BanchiGladiator.nome)    AS  Descrizione,
            LEN(AssBanchiGlad.idAtrezzo)                                    AS  matricola_len
    FROM    AssAttrezzi
    INNER JOIN AssBanchiGlad ON AssBanchiGlad.idAtrezzo = AssAttrezzi.attrezzoID
    INNER JOIN BanchiGladiator ON BanchiGladiator.ID = AssBanchiGlad.bancoID 
    WHERE   AssAttrezzi.cantiereID = 1
)
ORDER BY LEN(matricola),
        matricola

Order by with UNION statement

2 Answers2