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

  • 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. – Pred Dec 08 '21 at 21:33
  • 3
    Does this answer your question? [SQL Query to get column values that correspond with MAX value of another column?](https://stackoverflow.com/questions/6807854/sql-query-to-get-column-values-that-correspond-with-max-value-of-another-column) – Pred Dec 08 '21 at 21:34

2 Answers2

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;
Himanshu
  • 3,830
  • 2
  • 10
  • 29
-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.

procopypaster
  • 416
  • 1
  • 6
  • 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? –  Dec 08 '21 at 21:40
  • 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 – procopypaster Dec 08 '21 at 21:43
  • Ahh, totally clear. Thanks for your help! –  Dec 09 '21 at 09:24