By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
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