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:

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:

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

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.

• sql
• laravel
• postgresql
• sql-order-by

6 Answers 6

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.

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:

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

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

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

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;

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).

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
                   ); 

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

maybe you can try this

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

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