SQLite 3.39

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