Which row is returned using DISTINCT ON in Postgres

Question

When I use DISTINCT ON in PostgreSQL (distinct in django) which rows are retrieved in the group of rows with same fields?

the one that comes at first – Exprator Dec 12 '17 at 07:38 — Exprator, Dec 12 '17 at 07:38
Distinct doesn't delete anything – Sayse Dec 12 '17 at 07:38 — Sayse, Dec 12 '17 at 07:38
@Sayse by delete I mean which row is retrieved ? – mohammad Dec 12 '17 at 07:39 — mohammad, Dec 12 '17 at 07:39

score 3 · Answer 1 · answered Dec 12 '17 at 08:06

The documentation says:

A set of rows for which all the expressions are equal are considered duplicates, and only the first row of the set is kept in the output. Note that the "first row" of a set is unpredictable unless the query is sorted on enough columns to guarantee a unique ordering of the rows arriving at the DISTINCT filter.

So if you add an ORDER BY clause, the first row in that order is kept.

Without an ORDER BY clause, there is no way of telling which row will be kept.

yes, i tried some but each time the result is changed. need order to get static result. — Nam Nguyễn, Dec 12 '17 at 08:19

Which row is returned using DISTINCT ON in Postgres

1 Answers1