1

I need to get values from different columns (in one record) where one column has a maximum value. How do I select the maximum value of one column and all the other columns from the same record?

SELECT MAX(bid_price) 
FROM oc_product_auctionpack_customers pauc 
LEFT JOIN oc_product_auctionpack pau ON (pauc.bid_product_id = pau.product_id) 
WHERE bid_product_id = '" . $product_id . "'

This only selects the highest value of the selected column

Improve this question
2
  • Which RDBMS are you using (MSSQL, MySQL, Oracle, Postgres, etc)? You are wide open for SQL Injection, use parameterized/prepared statements and don't concatenate the query string with user inputs directly. Commented Dec 8, 2021 at 21:33
  • 3
    Does this answer your question? SQL Query to get column values that correspond with MAX value of another column? Commented Dec 8, 2021 at 21:34

2 Answers 2

Reset to default
2

Try using row_number() 

Select * from (
SELECT pauc.*, pau.*, row_number() over (order by
bid_price desc) rn 
FROM oc_product_auctionpack_customers pauc 
LEFT JOIN oc_product_auctionpack pau ON (pauc.bid_product_id = pau.product_id) 
WHERE bid_product_id = '" . $product_id . "') 
Where rn=1;
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

-1

I can't comment on other posters answers yet since my reputation isn't high enough, Himanshu's answer is definitely more robust. I'd add that you can use RANK() instead of ROW_NUMBER() in the event that you have two max bid prices that are the same.

You should probably be able to use group by -

SELECT MAX(bid_price) 
var_1,
var_2,
var_3
FROM oc_product_auctionpack_customers pauc 
LEFT JOIN oc_product_auctionpack pau ON (pauc.bid_product_id = pau.product_id) 
WHERE bid_product_id = '" . $product_id . "'
GROUP BY var_1, var_2, var_3;

The values brought in by var_1, var_2, var_3 would be distinct relative to the max value.

Unless you mean you want one record, you could do the same with the following additions -

SELECT MAX(bid_price) 
var_1,
var_2,
var_3
FROM oc_product_auctionpack_customers pauc 
LEFT JOIN oc_product_auctionpack pau ON (pauc.bid_product_id = pau.product_id) 
WHERE bid_product_id = '" . $product_id . "'
GROUP BY var_1, var_2, var_3
ORDER BY MAX(bid_price) DESC
LIMIT 1;

This should return only the single highest record.

Improve this answer

3 Comments

Thanks! I edited my question, yes I need only the single highest record (one record only). Looks like I need to SELECT (MAX(bid_price) customer_id, ... ... ... if I need to get the customer_id column value that corresponds the record with the highest bid_price column value. Is that it?
Yes, that would do it, I edited my response slightly as well, Himanshu's answer won't require you to add a var_x for every new item you want to bring in. If you have multiple of the same high bid_price and you want all to display you could swap row_number() for rank() in the partition. Rank would assign the value 1 to all records with the high bid_price using bid_price desc
Ahh, totally clear. Thanks for your help!