Typeorm order by after distinct on with postgresql

Question

I have a table below:

id	product_id	price
1	1	100
2	1	150
3	2	120
4	2	190
5	3	100
6	3	80

I want to select cheapest price for product and sort them by price

Expected output:

id	product_id	price
6	3	80
1	1	100
3	2	120

What I try so far:

`
 repository.createQueryBuilder('products')
.orderBy('products.id')
.distinctOn(['products.id'])
.addOrderBy('price')
`

This query returns, cheapest products but not sort them. So, addOrderBy doesn't effect to products. Is there a way to sort products after distinctOn ?

Answer 1

SELECT id,
       product_id,
       price
FROM   (SELECT id,
               product_id,
               price,
               Dense_rank()
                 OVER (
                   partition BY product_id
                   ORDER BY price ASC) dr
        FROM   product) inline_view
WHERE  dr = 1
ORDER  BY price ASC; 

Setup:

postgres=# create table product(id int, product_id int, price int);
CREATE TABLE
postgres=# insert into product values (1,1,100),(2,1,150),(3,2,120),(4,2,190),(5,3,100),(6,3,80);
INSERT 0 6

Output

id | product_id | price
----+------------+-------
  6 |          3 |    80
  1 |          1 |   100
  3 |          2 |   120
(3 rows)