select DISTINCT but dont mess with the order of rows
Tables, views, query results, etc. in relational databases represent relations. Relations are a special form of (multi) sets. Sets don't have an order. So the DBMS is free to deliver a result of a query in any order it "wants" unless there's an explicit ORDER BY
. That's why you get "random" results.
So use proper ORDER BY
clauses for DISTINCT ON
and row_number()
. Use an outer query with an ORDER BY
to order the end result.
SELECT rank,
user_id,
text
FROM (SELECT DISTINCT ON (user_id)
row_number() OVER (ORDER BY id) AS rank,
user_id,
text
FROM posts
ORDER BY user_id,
id) AS x
ORDER BY rank;
db<>fiddle