I want to order all the records by id ASC but the one record with document_status RESULTANT has to be last so that it looks like this

This is what I tried but it completely ignores my second ORDER condition

SELECT 
l.id,
l.document_number,
l.document_status
FROM load_record l
ORDER BY l.id ASC, (l.document_status = 'RESULTANT')

It works if I remove the l.id ASC but then it will place the RESULTANT last but it won’t order the rest of the records by id ASC

>Solution :

Inside the ORDER BY clause, the subsequent ordering fields (in your case, the second with respect to the first) are consulted only when the previous condition is tied, but in your case "l.id ASC" is never tied, so "l.document_status = ‘RESULTANT’" is never checked.

If you invert the fields inside your ORDER BY clause, it should work properly, because l.document_status = 'RESULTANT'* actually generates tied values, untied by l.id.

ORDER BY l.document_status = 'RESULTANT', l.id

Check the demo here.

Note: You can omit ASC, because it’s the default ordering value.