CODEWITHSUNDEEP
mysqlsqlgroup-byhaving
ajay_t
ajay_t

Reputation: 2385

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.

Upvotes: 1

Views: 625

Answers (4)

Akshey Bhat
Akshey Bhat

Reputation: 8545

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

Upvotes: 3

Rohit Gaikwad
Rohit Gaikwad

Reputation: 3904

Use the below query:

SELECT * FROM (SELECT * FROM user_bid  ORDER BY price DESC) AS Temp 
GROUP BY prod_id

Output:

Image

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520878

Here is an option using a join:

SELECT p1.*
FROM prod_id
INNER JOIN
(
    SELECT prod_id, MAX(price) AS price    -- this inner subquery finds the maximum
    FROM user_bid                          -- price for each product group
    GROUP BY prod_id
) p2
    ON p1.prod_id = p2.prod_id AND         -- and this join condition retains
       p1.price   = p2.price               -- only records which have this maximum
                                           -- price for each product group

And here is an option which uses a subquery:

SELECT p1.*
FROM prod_id p1
WHERE price = (SELECT MAX(p2.price) FROM prod_id p2 WHERE p1.prod_id = p2.prod_id)

Upvotes: 3

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

For SQL Server

;WITH cte AS

(
SELECT prod_id,price,user_id,
       ROW_NUMBER() OVER(PARTITION BY prod_id ORDER BY price desc) as rank
FROM table Name
)

SELECT prod_id,price,user_id
FROM cte
WHERE rank=1

Upvotes: 1

Related Questions