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 ?

rajorshi · Accepted Answer · 2022-12-03 18:54:29Z

2

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)

edited Dec 3, 2022 at 18:54

answered Jan 30, 2022 at 9:00

rajorshi

7174 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Typeorm order by after distinct on with postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related