CODEWITHSUNDEEP
mysqlsqlsql-order-by
Desire
Desire

Reputation: 107

Select top one record for each unique value of column

I've this table structure

I want top one record order by price but for each unique value of request_id, for example say if I've 

request_id  user_id price
       1,       1,  100
       1,       2,  200
       1,       3,  300                     
       2,       2,  10
       2,       1,  20
       2,       3,  30

Then the desired result would be:

request_id  user_id price
       1,       1,  100
       2,       2,  10

SELECT user_id,price FROM request ORDER BY price LIMIT 1

Upvotes: 1

Views: 1611

Answers (1)

Strawberry
Strawberry

Reputation: 33935

SELECT x.* 
  FROM my_table x
  JOIN 
     ( SELECT request_id,MIN(price) min_price FROM my_table GROUP BY request_id )y
    ON y.request_id = x.request_id
   AND y.min_price = x.price;

Upvotes: 1

Related Questions