SQL ASC and DESC ordering on same column based on another column

Question

I want to arrange pending orders by eta in asc order and completed orders in desc order, with all completed orders appearing at the bottom of the list. Not sure if this is possible in a single query.

Example data from orders table:

order	status	eta
1	pending	20-01-2025
2	complete	15-01-2025
3	ordered	28-01-2025
4	complete	16-01-2025
5	sailing	25-01-2025

I would like the above data to be displayed as follows:

order	status	eta
1	pending	20-01-2025
3	sailing	25-01-2025
5	ordered	28-01-2025
4	complete	16-01-2025
2	complete	15-01-2025

in the above result only the eta column is ordered, where all 'complete' orders at the bottom

I am using Laravel to build my app, with a Postgres backend, but I am sure any SQL solution will help. I am trying to avoid running separate queries and concat them.

Answer 1

SELECT "order", status, eta
FROM   tbl
ORDER  BY status = 'complete'
     , EXTRACT(epoch FROM eta) * CASE WHEN status = 'complete' THEN -1 ELSE 1 END;

fiddle

The status = 'complete' evaluates to boolean, where false sorts before true.
If the column is defined NOT NULL, you are all set.
If the column isn't defined NOT NULL, use status IS NOT DISTINCT FROM 'complete' instead to make sure only 'completed' is sorted last.

• Sorting null values after all others, except special
• Sort NULL values to the end of a table

ASC and DESC are syntax elements and cannot be parameterized. For data types that can be "inverted", like numeric types, you can work around this limitation by multplying with -1 for descending order. So I convert the date with EXTRACT() and then conditionally multiply with -1. Here, a simple WHEN status = 'complete' covers null values, the first CASE branch is only entered on true.

Or an optimized variant of what you found yourself:

SELECT "order", status, eta
FROM   tbl
ORDER  BY status = 'complete'
     , CASE WHEN status = 'complete' THEN null ELSE eta END
     , CASE WHEN status = 'complete' THEN eta END DESC;

fiddle

All nice trickery. But it won't use plain indexes. This is more verbose, but less confusing and typically faster with an index on (status, eta):

(  -- parentheses required
SELECT "order", status, eta
FROM   tbl
WHERE  status <> 'complete'
ORDER  BY eta
)
UNION ALL 
(
SELECT "order", status, eta
FROM   tbl
WHERE  status = 'complete'
ORDER  BY eta DESC
);

fiddle

See:

• Switch ASC / DESC in ORDER BY with a CASE construct?
• Special variable FOUND not set properly?

And avoid reserved words like "order" as identifiers.