DISTINCT ON Works on PostgreSQL Only

DISTINCT ON (customer_id) is a neat way to keep each customer’s latest order on PostgreSQL, and none of the other three engines has it.

SQLite, MySQL and MariaDB report a syntax error. Window functions say the same thing on all four (SQLite 3.25, MySQL 8.0 and MariaDB 10.2 and later).

What Each Engine Answered

On a table made with

CREATE TABLE t (a INTEGER, b INTEGER);
INSERT INTO t VALUES (1, 5), (1, 3), (2, 4);

the example

SELECT DISTINCT ON (a) a, b FROM t ORDER BY a, b;

gave these answers:

What to Write Instead

This form answers the same on every engine:

SELECT a, b FROM (SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY b) AS n FROM t) AS s WHERE n = 1 ORDER BY a;

Number the rows of each group and keep the first: SELECT a, b FROM (SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY b) AS n FROM t) AS s WHERE n = 1.

What BareSQL Portable Reports

Saved as example.sql, the example gets this from baresql lint --all:

$ baresql lint --all example.sql
example.sql:3:8: error: DISTINCT ON (a) keeps one row per value of a on PostgreSQL only. [distinct-on]
    DISTINCT ON (a)
    Number the rows of each group and keep the first: SELECT a, b FROM (SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY b) AS n FROM t) AS s WHERE n = 1.
example.sql:3:45: info: SQLite, MySQL and MariaDB put NULL first in ascending order; PostgreSQL puts it last. [null-order]
    a
    Sort on x IS NULL first: ORDER BY x IS NULL, x puts NULL last on every engine, and ORDER BY x IS NOT NULL, x puts it first. NULLS FIRST and NULLS LAST do not work on MySQL and MariaDB.
example.sql:3:48: info: SQLite, MySQL and MariaDB put NULL first in ascending order; PostgreSQL puts it last. [null-order]
    b
    Sort on x IS NULL first: ORDER BY x IS NULL, x puts NULL last on every engine, and ORDER BY x IS NOT NULL, x puts it first. NULLS FIRST and NULLS LAST do not work on MySQL and MariaDB.

3 problems: 1 error, 2 notes.

The example also runs into Where NULL sorts, which has a post of its own.

baresql rules distinct-on prints the same answers in the terminal, and the BareSQL Portable page lists every difference BareSQL knows.