Typeorm order by after distinct on with postgresql

0
votes

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 ?

1
postgresqlsql-order-bydistincttypeormdistinct-on

1 Answers

0
votes
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=# inseert into 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)