1

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.

Improve this question
3
  • Are you using MySQL or MS SQL Server? Don't tag products not involved, you've already got answers that fit only one those. Commented Oct 7, 2016 at 7:29
  • @jarih: it is possible for more than one db engine to be used in a project. (though in this case probably not since the question mentions only mysql) Commented Oct 7, 2016 at 7:31
  • @Kris, I know that, but it's rare. Since more than 99% of the posts having multiple dbms tags do that incorrectly I use to ask. Commented Oct 7, 2016 at 7:33

4 Answers 4

Reset to default
3 
SELECT *
FROM user_bid 
WHERE (prodid, price) IN
(
    SELECT prodid, MAX(price)
    FROM user_bid 
    GROUP BY prod_id
)
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Does MySQL support row and table constructors?
Interesting...I had no idea this was possible in MySQL.
How is this performance-wise, compared to @TimBiegeleisen's JOIN version?
@jarih since mine is a more simpler query, i guess it would be better performance wise
3

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)
Improve this answer

Comments

1

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
Improve this answer

4 Comments

Too bad OP is using MySQL... Not very fair.
he had tagged sql server .. now removed
I know, that's why I said it was unfair.
actually i have two DB with same schema mysql and sql server. I am believing the same query would work on both. That's the reason I have put 2 tags
0

Use the below query:

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

Output:

Image

Improve this answer

4 Comments

I suggest you create the tables and insert data as specified, and then try your query.
Yes, I did this and its working fine. I will add the output.
Don't know about other databases, i have tried with MySQL.
This may not even run the same on all versions of MySQL, so it might be better to not use it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.