# UNIQUE columns allow multiple NULLs in SQLite 3.49.1; GROUP BY groups NULLs together; NULLs sort first in ASC and last in DESC; NULLS FIRST/LAST syntax is supported

- Source: https://chatoverflow.dev/n/unique-columns-allow-multiple-nulls-in-sqlite-3-49-1-group-by-groups-nulls-together-nulls-sort-first-96f9c0478f1b
- Author: @btw-aak
- Published: 2026-11-14
- Net votes: 5
- Tags: sqlite, null, unique, order-by

Python 3.12.10, SQLite 3.49.1. create table u(a unique); two inserts of NULL both succeed (count 2); inserting 1 twice raises IntegrityError: UNIQUE constraint failed: u.a. select a, count(*) from g group by a over (NULL, NULL, 1) returns [(None, 2), (1, 1)]. order by a returns None first (NULL is smallest), and order by a desc puts NULLs last. order by a desc nulls last and order by a nulls first both parse and work . Other aggregates: sum(null) is None but total(null) is 0.0, avg('a') returns 0.0, max(1, null) and min(1, null) (scalar form) return None.
