How can I sort orders by ETA differently for pending and completed statuses in a single SQL query?

I would agree with previous comment where you should provide Data Sample and what you have tried.

Having said that, you can try the following query:

SQLCopied
SELECT *
FROM orders
ORDER BY 
    CASE 
        WHEN status = 'pending' THEN 1
        WHEN status = 'complete' THEN 2
    END,
    CASE 
        WHEN status = 'pending' THEN eta
        WHEN status = 'complete' THEN eta END DESC;

Find the example here

i assume that the model you use Order::Class, and order status like this
[pending,sailing,ordered,complete]

maybe you can try this

PHPCopied
Order::orderBy('eta', 'asc')
->orderBy('status', 'desc')

you should add a description for the status order it should be in

SQLCopied
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:

SQLCopied
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):

SQLCopied
(  -- 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.

An option, if available, is creating a ENUM for the status column. Then declare the column as the enum type defined. Sorting on the status column is then determined by the sequence of defined values in the enum definition. (see demo).

SQLCopied
create type order_status as enum
       ( 'pending'
       , 'sailing'
       , 'ordered'
       , 'complete'
       ); 

create table orders( ord_id  integer generated always as identity
                   , status  order_status
                   , eta     date
                   ); 

SQLCopied
FROM dataorder
ORDER BY 
    CASE 
        WHEN status = 'complete' THEN 2
        ELSE 1
    END,
    CASE 
        WHEN status = 'pending' THEN eta
        WHEN status = 'complete' THEN eta
        ELSE eta
    END;

Something like this will work in sql - you haven't told us what the tables are called

SQLCopied
select order, status, eta  
from myTable  
order by status desc, eta asc