What does DISTINCT ON expression do

Question

I am aware of how DISTINCT operates, but I am baffled by DISTINCT ON (expression).

Consider the first illustration in this screenshot:

What effect does the portion (a% 2) have on everything? Is it saying to just return if the returned value is distinct yet a% 2 evaluates to true before continuing to do so for all other tuples?

Answer 1

The snippet from the Official documentation for PostgreSQL is as follows.

DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. [...] Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first. [...] The DISTINCT ON expression(s) must match the leftmost ORDER BY expression(s).

The first thing to note is that, for reasons that, hopefully, will soon become evident, whatever you put in the ON (), must be first in the ORDER BY.

SELECT DISTINCT ON (a) a, b, c FROM a_table ORDER BY a, b

The results are then filtered such that just the first row is really returned for each of the different entities.

For example...

CREATE TABLE example (
    id               INT,
    person_id        INT,
    address_id       INT,
    effective_date   DATE
);

INSERT INTO
    example (id, person_id, address_id, effective_date)
VALUES
    (1, 2, 1, '2000-01-01'),  -- Moved to first house
    (5, 2, 2, '2004-08-19'),  -- Went to uni
    (9, 2, 1, '2007-06-12'),  -- Moved back home

    (2, 4, 3, '2007-05-18'),  -- Moved to first house
    (3, 4, 4, '2016-02-09')   -- Moved to new house
;

SELECT DISTINCT ON (person_id)
    *
FROM
    example
ORDER BY
    person_id,
    effective_date DESC
;

I hope this helps you.