How to get all unique records from the database?

Question

PostgreSQL

There are 2 tables: products and product_prices , they are linked by the key products.id = product_prices.product_id

And the query:

SELECT DISTINCT ON ( product_prices.product_id) product_id, slug, city_id 
FROM"products"
  LEFT JOIN "product_prices" ON "product_prices"."product_id" = "products"."id" 
ORDER BY city_id asc LIMIT 30

The problem is that I need to do order by city_id asc

Instead of sorted records, I get an error:
ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions

When I add product_id, city_id asc to ORDER BY, it sorts by product_id

How to solve such a problem?

Answer 1

distinct on requires using order by on the same fields and some other criteria for determining the "first" row.
If you need some other sorting as a result of the entire query that does not match this one, wrap the query in a subquery and resort as needed again.
select ... from (...) subquery order by ...

Answer 2

"When I add to ORDER BY product_id, city_id asc it sorts by product_id"
ORDER BY city_id asc, product_id did not try? :)