Postgres 17

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tbl (
  "order" int
, status text
, eta date
);

INSERT INTO tbl VALUES
  (1,	'pending' ,	'2025-01-20')
, (2,	'complete',	'2025-01-15')
, (3,	'ordered' ,	'2025-01-28')
, (4,	'complete',	'2025-01-16')
, (5,	'sailing' ,	'2025-01-25')
;

CREATE TABLE



INSERT 0 5


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


order
status
eta


1
pending
2025-01-20

5
sailing
2025-01-25

3
ordered
2025-01-28

4
complete
2025-01-16

2
complete
2025-01-15



SELECT 5


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;


order
status
eta


1
pending
2025-01-20

5
sailing
2025-01-25

3
ordered
2025-01-28

4
complete
2025-01-16

2
complete
2025-01-15



SELECT 5


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


order
status
eta


1
pending
2025-01-20

5
sailing
2025-01-25

3
ordered
2025-01-28

4
complete
2025-01-16

2
complete
2025-01-15



SELECT 5

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