Optimus Optimus - 2 months ago 6
MySQL Question

Mysql query with group by and having clause

I am constructing query to get the maximum price for same product.
I have table as

-----------------------------------------
| prod_id | price | user_id |
-----------------------------------------
| 4 | 25 | 1 |
| 4 | 38 | 2 |
| 4 | 41 | 3 |
| 7 | 100 | 1 |
| 7 | 95 | 2 |
-----------------------------------------


I am trying to get following output:

-----------------------------------------
| 4 | 41 | 3 |
-----------------------------------------
| 7 | 100 | 1 |
-----------------------------------------


I have constructed following query which is not right.

select * from user_bid group by prod_id having max(price);


Can someone guide me to get the query for desired results.

Answer
SELECT *
FROM user_bid 
WHERE (prodid, price) IN
(
    SELECT prodid, MAX(price)
    FROM user_bid 
    GROUP BY prod_id
)