How do I select all columns that belong to one columns MAX() value in SQL?-CodePudding

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

CodePudding user response：

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;

CodePudding user response：

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.