Multiple DISTINCT ON clauses in PostgreSQL

Question

Is it possible to select rows that are DISTINCT ON some separate, independent sets of columns?

Suppose I want all the rows which match the following conditions:

distinct on (name, birth)
distinct on (name, height)

So that, out of the following table, the rows marked with a red cross would not be distinct (with an indication of the failing clause):

name      birth    height
--------------------------
William    1976      1.82
James      1981      1.68
Mike       1976      1.68
Tom        1967      1.79
William    1976      1.74   ❌ (name, birth)
William    1981      1.82   ❌ (name, height)
Tom        1978      1.92
Mike       1963      1.68   ❌ (name, height)
Tom        1971      1.86
James      1981      1.77   ❌ (name, birth)
Tom        1971      1.89   ❌ (name, birth)

In the above example, if the DISTINCT ON clause had just been DISTINCT ON (name, birth, height), then all the rows would have been considered distinct.

Tried and didn't work:

SELECT DISTINCT ON (name, birth) (name, height) ...
SELECT DISTINCT ON (name, birth), (name, height) ...
SELECT DISTINCT ON ((name, birth), (name, height)) ...
SELECT DISTINCT ON (name, birth) AND (name, height) ...
SELECT DISTINCT ON (name, birth) AND ON (name, height) ...
SELECT DISTINCT ON (name, birth) DISTINCT ON (name, height) ...
SELECT DISTINCT ON (name, birth), DISTINCT ON (name, height) ...

Postgres allows `DISTINCT ON ` on expressions. Maybe you want something like `SELECT DISTINCT ON (name||birth, name||height)` ? — Eugene, Feb 28 '17 at 10:06
@Eugene very nice, seems to do the trick and with no obvious performance penalty. Why don't you turn it into an answer? — Jivan, Feb 28 '17 at 10:22
@Jivan Do you have ordering criteria? if not, you may get random rows, for example you maybe get `William | 1976 | 1.82` or may be `William | 1981| 1.82` — Oto Shavadze, Feb 28 '17 at 10:38
@OtoShavadze good precision — I guess this simple trick would not work with an ordering criteria — Jivan, Feb 28 '17 at 10:54
The definition of your problem is ***not deterministic***. The result could include `William 1976 1.82` or both `William 1976 1.74` *and* `William 1981 1.82 ` Either would pass your criteria. You need to define the objective more closely. (For instance by defining unambiguously which row to pick from each set of dupes.) And *always* provide your version of Postgres. — Erwin Brandstetter, Feb 28 '17 at 14:59

Erwin Brandstetter · Accepted Answer · 2022-01-16T02:14:40.170

As commented, there is ambiguity in the question. The number of result rows can differ for every call. If you are satisfied with arbitrary results, @klin's solution is good enough. Else, you need to define requiremens more closely. Like:
distinct on (name, birth), pick smallest height first, then smallest ID as tiebreaker

Or:
distinct on (name, height), pick earliest birth first, then smallest ID as tiebreaker

Your table should have a primary key (or some way to identify rows uniquely):

CREATE TEMP TABLE tbl (
  tbl_id serial PRIMARY KEY
, name text
, birth int
, height numeric);

INSERT INTO tbl (name, birth, height)
VALUES
  ('William', 1976, 1.82)
, ('James',   1981, 1.68)
, ('Mike',    1976, 1.68)
, ('Tom',     1967, 1.79)
, ('William', 1976, 1.74)
, ('William', 1981, 1.82)
, ('Tom',     1978, 1.92)
, ('Mike',    1963, 1.68)
, ('Tom',     1971, 1.86)
, ('James',   1981, 1.77)
, ('Tom',     1971, 1.89);

Query:

SELECT DISTINCT ON (name, height) *
FROM  (
   SELECT DISTINCT ON (name, birth) *
   FROM   tbl
   ORDER  BY name, birth, height, tbl_id  -- pick smallest height, ID as tiebreaker
   ) sub
ORDER  BY name, height, birth, tbl_id;    -- pick earliest birth, ID as tiebreaker

 tbl_id |  name   | birth | height
--------+---------+-------+--------
      2 | James   |  1981 |   1.68
      8 | Mike    |  1963 |   1.68
      4 | Tom     |  1967 |   1.79
      9 | Tom     |  1971 |   1.86
      7 | Tom     |  1978 |   1.92
      5 | William |  1976 |   1.74
      6 | William |  1981 |   1.82
(7 rows)    -- !!!

A DISTINCT ON query without deterministic ORDER BY can return any arbitrary row from each set of dupes. Applied once, you still get a deterministic number of rows (with arbitrary picks). Applied repeatedly, the resulting number of rows is arbitrary, too. Related:

Select first row in each GROUP BY group?

score 2 · Answer 2 · answered Feb 28 '17 at 10:09

Use a derived table:

with my_table(name, birth, height) as (
values
('William',    1976,      1.82),
('James',      1981,      1.68),
('Mike',       1976,      1.68),
('Tom',        1967,      1.79),
('William',    1976,      1.74),  -- ? (name, birth)
('William',    1981,      1.82),  -- ? (name, height)
('Tom',        1978,      1.92),
('Mike',       1963,      1.68),  -- ? (name, height)
('Tom',        1971,      1.86),
('James',      1981,      1.77),  -- ? (name, birth)
('Tom',        1971,      1.89)   -- ? (name, birth)
)
select distinct on (name, height) *
from (
    select distinct on (name, birth) *
    from my_table
    ) s

  name   | birth | height 
---------+-------+--------
 James   |  1981 |   1.68
 Mike    |  1963 |   1.68
 Tom     |  1967 |   1.79
 Tom     |  1971 |   1.89
 Tom     |  1978 |   1.92
 William |  1976 |   1.82
(6 rows)

Multiple DISTINCT ON clauses in PostgreSQL

2 Answers2

Linked