Sort by two date fields

Question

I have the following structure:

ID  |   PROMESSA    |   VENCIMENTO  
1   |   2019-01-04  |   2019-10-08
2   |   2019-01-03  |   2019-10-07
3   |   2019-01-02  |   2019-10-06
4   |   NULL        |   2019-10-05
5   |   NULL        |   2019-10-04
6   |   NULL        |   2019-10-03
7   |   NULL        |   2019-10-02
8   |   NULL        |   2019-10-01

I would like to select the records by sorting through the field PROMESSA growing, then through the countryside VENCIMENTO crescent.

The two fields are in format date.

I tried so : ORDER BY IF(PROMESSA IS NULL, 1, 0), PROMESSA ASC, VENCIMENTO ASC;

Expected result:

    ID  |   PROMESSA    |   VENCIMENTO
    3   |   2019-01-02  |   2019-10-06
    2   |   2019-01-03  |   2019-10-07
    1   |   2019-01-04  |   2019-10-08
    8   |   NULL        |   2019-10-01
    7   |   NULL        |   2019-10-02
    6   |   NULL        |   2019-10-03
    5   |   NULL        |   2019-10-04
    4   |   NULL        |   2019-10-05

Any suggestions?

Answer 1

Your solution only needs a small adjustment to generate the expected result. The solution is to change the IF.

Solution 1:

ORDER BY IF(PROMESSA IS NULL, 1, 0), PROMESSA ASC, VENCIMENTO ASC

If PROMISE is NULL then assign 1, otherwise assign 0. This change will ensure that records without PROMISE date are listed last.

Solution 2:

Similar but more concise.

ORDER BY IF(PROMESSA IS NULL, '9999-12-31', PROMESSA), VENCIMENTO ASC

If PROMISE is NULL, the date '9999-12-31' will be used as default. As this is the maximum date, for the DATE type, records under these conditions will be listed last.

Solution 3:

Portable solution*, and based on Sorack’s commentary, using COALESCE

ORDER BY COALESCE(PROMESSA, VENCIMENTO), VENCIMENTO

Note that if you use COALESCE as above, you have to add the MATURITY column again. This way ensures that if the two columns have a valid date, the sort will consider the two dates and not only the first one different from NULL.

*COALESCE is part of the ANSI-92 standard, and therefore exists in all database management systems that implement this standard (or later).