I'm doing the following select in a database and displaying the result in a table in php:
SELECT ID_PRDC, PRDC_ID_PRDT, PRDC_ID_SEM, DATA_PRDC, LINHA.NM_LN, PRODUTO.NM_PRDT,
SEMI_ACABADO, NUM_OP, LOTE, QTD, PRDC_NUM_SEQ_LN, SITUACAO.DESC_SIT, SITUACAO.ID_SIT, PRDC_ID_LN, PRDC_ID_SIT
FROM PRODUCAO
INNER JOIN produto on (PRODUCAO.PRDC_ID_PRDT = PRODUTO.ID_PRDT)
INNER JOIN linha on (PRODUCAO.PRDC_ID_LN = LINHA.ID_LN)
INNER JOIN situacao on (PRODUCAO.PRDC_ID_SIT = SITUACAO.ID_SIT)
WHERE PRDC_ID_SEM = $ult_sem AND DATE_FORMAT( DATA_PRDC, '%Y' ) = $ano_atual
ORDER BY PRDC_ID_LN, PRDC_NUM_SEQ_LN
Today the table is assembled and ordered in PHP by the line id sequence which is ideal. Each insert in the production table has a status that is related to the situation table, which I would like is when a production is updated to the completed status > or canceled it would be displayed at the end of the list, keeping the remainder sorted by line id.
I tried with 2 SELECTS (one sorted by line and one by status) with UNION ALL but it does not recognize an ORDER BY for each select, I tried to sort first by id_status and then by id_line but the result sorts only by id_status and practically ignores the ordering by id_line.
Example current result:
+------------------------------------------------------------+
| PRODUCAO |
+------------------+--------------+----------+---------------+
| COD PRODUTO | PRODUTO | LINHA | STATUS |
+------------------------------------------------------------+
| 1000001 | prod1 | 1-linha1 | 2-Produzindo |
| 2000002 | prod2 | 1-linha1 | 6-Concluido |
| 2000007 | prod7 | 2-linha2 | 7-Cancelado |
| 2000003 | prod3 | 2-linha2 | 1-Aguardando |
| 2000005 | prod5 | 3-linha3 | 6-Concluido |
| 3000006 | prod6 | 4-linha4 | 2-Produzindo |
| 4000004 | prod4 | 4-linha4 | 1-Aguardando |
+------------+-----------------------------------------------+
Example of optimal result:
+------------------------------------------------------------+
| PRODUCAO |
+------------------+--------------+----------+---------------+
| COD PRODUTO | PRODUTO | LINHA | STATUS |
+------------------------------------------------------------+
| 1000001 | prod1 | 1-linha1 | 2-Produzindo |
| 2000007 | prod7 | 2-linha2 | 1-Aguardando |
| 2000005 | prod5 | 3-linha3 | 2-Produzindo |
| 2000004 | prod4 | 4-linha4 | 1-Aguardando |
| 2000003 | prod3 | 2-linha2 | 6-Concluido |
| 3000006 | prod6 | 4-linha4 | 6-Concluido |
| 4000002 | prod2 | 1-linha1 | 7-Cancelado |
+------------+-----------------------------------------------+
If someone can help thank you I already researched a lot but I did not find any similar example.