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
Advertisement
Answer
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.