Question 422254 in Laravel Question posted in
The official Laravel Documentation can be found here.

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

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.

Tags:

6

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
  3. 0 
    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.

    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 multiply with the same expression. In this case a simple WHEN status = 'complete' covers null values as well, as only the first 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:

    And avoid reserved words like "order" as identifiers.

    Login or Signup to reply.
  4. 0

    It is simple to Sort the records by One Column in ASC and Another Column in DSC You can just use:
    SQL Query Syntax: SELECT Col1,Col2,… FROM TABLE ORDER BY ASC Col1, DESC Col2;
    for further assistance refer this W3 Schools Website Link:See Example 2 Well it is for MYSQL But most of them should work.

    Login or Signup to reply.
  5. 0 
    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;
    Login or Signup to reply.
  6. 0

    An option, if available, is creating a ENUM for the status column. Then declare the column as the enum type defined. Sorting is then determined by the sequence of defined values in the enum. (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
                   );
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search