Mike Pala Mike Pala - 2 months ago 8
SQL Question

SQL name value with highest value in another column

let's say I have a table like so:

Store | Item | Price
store01 | Apple | 2.50
store01 | Pear | 3.00
store01 | Banana | 3.11
store02 | Apple | 2.50
store02 | Pear | 2.00
store03 | Banana | 3.10


and I just want a query that lists all the stores and names the most expensive item in that store. So I need something like this:

Store | Item
store01 | Banana
store02 | Apple
store03 | Banana


I tried something like so:

SELECT "Store",
(case when (max ("Price") = "Price") then "Item" end) as "Max price Item"
FROM Table
group by "Price","Item","Store";


but the result of that is just:

Store | Max price Item
store01 | Apple
store01 | Pear
store01 | Banana
store02 | Apple
store02 | Pear
store03 | Banana


I am running on dashDB.

Answer

The following should do the trick:

 SELECT Store, MAX(Price) FROM Table
 GROUP BY Store

Or

 SELECT
  b.Store,
  MAX(b.Price) as MaxPrice,
  MAX(b.Item) as Item
FROM Table b
INNER JOIN (SELECT 
              Store,
              MAX(Price) as MaxPrice
            FROM Table
            GROUP BY Store) a ON 
a.Store = b.Store AND a.MaxPrice = b.Price
GROUP BY b.Store

Sample inputs and outputs:

sample_input

sample_output

Comments