Make SELECT display a certain value of a column last, keeping ordering by another column

0

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.

    
asked by anonymous 22.07.2016 / 14:10

0 answers