Can my DB?

DISTINCT ON

Return one row per distinct combination of the listed columns, with ORDER BY picking which row to keep. PostgreSQL-only.

DISTINCT ON SELECT DISTINCT ON first row per group
distinct deduplication first row per group PostgreSQL
DatabaseStatusSyntax FamilyMin Version
PostgreSQL full distinct-on -
MySQL none - -
MariaDB none - -
Oracle none - -
SQL Server none - -
SQLite none - -
Redshift none - -
DB2 none - -
Snowflake none - -
BigQuery none - -

Compare Databases

Database Details

PostgreSQL full 
SELECT DISTINCT ON (customer_id)
  customer_id, order_date, total
FROM orders
ORDER BY customer_id, order_date DESC;
  • ORDER BY must begin with the DISTINCT ON columns; the row kept per group is the first one in the remaining ORDER BY sequence.
  • Often plans better than the equivalent ROW_NUMBER() subquery because the planner can stop after the first row per group.
Docs: SELECT DISTINCT ON
MySQL none 
SELECT customer_id, order_date, total
FROM (
  SELECT customer_id, order_date, total,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
  FROM orders
) ranked
WHERE rn = 1;
  • Use ROW_NUMBER() in a subquery and filter rn = 1 (window functions require 8.0+).
Docs: Window Functions (MySQL)
MariaDB none 
SELECT customer_id, order_date, total
FROM (
  SELECT customer_id, order_date, total,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
  FROM orders
) ranked
WHERE rn = 1;
  • Use ROW_NUMBER() in a subquery (window functions require 10.2+).
Docs: Window Functions (MariaDB)
Oracle none 
SELECT customer_id, order_date, total
FROM (
  SELECT customer_id, order_date, total,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
  FROM orders
)
WHERE rn = 1;
  • Use ROW_NUMBER() in a subquery and filter rn = 1.
Docs: ROW_NUMBER (Oracle)
SQL Server none 
SELECT customer_id, order_date, total
FROM (
  SELECT customer_id, order_date, total,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
  FROM orders
) ranked
WHERE rn = 1;
  • Use ROW_NUMBER() in a subquery, or TOP 1 WITH TIES combined with ORDER BY ROW_NUMBER() OVER (...).
Docs: ROW_NUMBER (Transact-SQL)
SQLite none 
SELECT customer_id, order_date, total
FROM (
  SELECT customer_id, order_date, total,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
  FROM orders
) ranked
WHERE rn = 1;
  • Use ROW_NUMBER() in a subquery (window functions require 3.25.0+, 2018-09-15).
Docs: Window Functions (SQLite)
Redshift none 
SELECT customer_id, order_date, total
FROM (
  SELECT customer_id, order_date, total,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
  FROM orders
) ranked
WHERE rn = 1;
  • Use ROW_NUMBER() in a subquery or CTE and filter rn = 1.
Docs: Window Functions (Redshift)
DB2 none 
SELECT customer_id, order_date, total
FROM (
  SELECT customer_id, order_date, total,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
  FROM orders
) ranked
WHERE rn = 1;
  • Use ROW_NUMBER() in a subquery or CTE and filter rn = 1.
Docs: ROW_NUMBER (IBM Db2)
Snowflake none 
SELECT customer_id, order_date, total
FROM orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) = 1;
  • With QUALIFY, the most concise equivalent is QUALIFY ROW_NUMBER() OVER (PARTITION BY col ORDER BY sort_col) = 1 — no subquery needed.
Docs: QUALIFY Clause (Snowflake)
BigQuery none 
SELECT customer_id, order_date, total
FROM (
  SELECT customer_id, order_date, total,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
  FROM orders
) ranked
WHERE rn = 1;
  • Use ROW_NUMBER() in a subquery, or inline with QUALIFY ROW_NUMBER() OVER (...) = 1.
Docs: Window Function Calls (BigQuery)