By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE t (name, year);
✓
INSERT INTO t VALUES
('A', 2014), ('A', 2013), ('V', null), ('Z', null), ('B', 2013), ('W', null);
✓
SELECT *
FROM t
ORDER BY year NULLS LAST,
CASE WHEN year IS NOT NULL THEN name END,
CASE WHEN year IS NULL THEN name END DESC;
| name | year |
|---|---|
| A | 2013 |
| B | 2013 |
| A | 2014 |
| Z | null |
| W | null |
| V | null |
SELECT *
FROM t
ORDER BY year IS NULL,
year,
CASE WHEN year IS NOT NULL THEN name END,
CASE WHEN year IS NULL THEN name END DESC;
| name | year |
|---|---|
| A | 2013 |
| B | 2013 |
| A | 2014 |
| Z | null |
| W | null |
| V | null |
SELECT *
FROM t
ORDER BY CASE WHEN year IS NOT NULL THEN 1 ELSE 2 END,
year,
CASE WHEN year IS NOT NULL THEN name END,
CASE WHEN year IS NULL THEN name END DESC;
| name | year |
|---|---|
| A | 2013 |
| B | 2013 |
| A | 2014 |
| Z | null |
| W | null |
| V | null |