PostgreSQL DISTINCT ON forces a specific order

All we need is an easy explanation of the problem, so here it is.

Why DISTINCT ON is sorting the rows? E.g. the query

SELECT DISTINCT ON (a) a
FROM (
  VALUES (2), (3), (1)
) x(a);

will have the result

1
2
3

while without DISTINCT ON

SELECT a
FROM (
  VALUES (2), (3), (1)
) x(a);

I’ll get the correct order

2
3
1

How can I preserve the natural order of defined values? By "natural order" I mean "the order in which the values were defined". I understand that DBMS may change the order if I don’t specify it explicitly with an ORDER BY, but I was not expecting that DISTINCT ON my interfere in this way.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Why DISTINCT ON is sorting the rows?

Distinct removes "duplicate" rows from the result set. That’s a whole lot easier to do if the items being compared arrive neatly sorted into order.

Check the Explain Plan for this query – it will probably contain extra "Sort" and "Unique" steps. Guess what those are doing.

"select distinct" is always a Red Flag for me.
It is almost always used poorly, a band-aid to get around a bigger problem and introduces problems of its own. Remember, the performance of …

select distinct a, b, c 
. . . 

… is roughly comparable to that of …

select a, b, c 
. . . 
group by a, b, c 
order by a, b, c 

Ask yourself why [you think] you need to use "distinct".

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply