SQL Server 2022

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

select @@version;


(No column name)


Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) 
 Oct  8 2022 05:58:25 
 Copyright (C) 2022 Microsoft Corporation
 Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



CREATE TABLE orders (
    id INTEGER IDENTITY PRIMARY KEY, 
    order_number INT NOT NULL,
    status VARCHAR(20) NOT NULL, 
    eta DATE NOT NULL 
);


INSERT INTO orders (order_number, status, eta) VALUES
(1, 'pending', '2025-01-20'),
(2, 'complete', '2025-01-15'),
(3, 'pending', '2025-01-21'),
(4, 'complete', '2025-01-16');
4 rows affected

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;


id
order_number
status
eta


3
3
pending
2025-01-21

1
1
pending
2025-01-20

4
4
complete
2025-01-16

2
2
complete
2025-01-15

id	order_number	status	eta
3	3	pending	2025-01-21
1	1	pending	2025-01-20
4	4	complete	2025-01-16
2	2	complete	2025-01-15