In MySQL, I try to select a corresponding value based on the greatest value between columns.

Let's take as example table 'prices':

The result I intend to get is:

Is there a possibillity to avoid a self join? Or is the self join just the way to do it?

CodePudding user response：

If you are using MySQL 8

select distinct
 id, 
 sum(case when supplier = 'Microsoft' then price1 else 0 end) over by_id price_ms,
 sum(case when supplier = 'Apple' then price1 else 0 end) over by_id price_apple,
 sum(case when supplier = 'IBM' then price1 else 0 end) over by_id price_ibm,
 min(price1) over by_id best_price,
 first_value(quantity1) over by_id_price best_price_qty
from prices
window 
by_id as (partition by id),
by_id_price as (partition by id order by price1 asc)

Demo

CodePudding user response：

This is the solution I came up with which includes the self join. And I believe it's unavoidable the more I think about it.

SELECT
   id,
   SUM(CASE WHEN t1.supplier = 'Microsoft' THEN t1.price1 ELSE 0 END) AS 'price_microsoft',
   SUM(CASE WHEN t1.supplier = 'Apple' THEN t1.price1 ELSE 0 END) AS 'price_apple',
   SUM(CASE WHEN t1.supplier = 'IBM' THEN t1.price1 ELSE 0 END) AS 'price_ibm' 
   t2.price1 AS ‘best price’,
   t2.quantity1 AS ‘best price corresponding quantity’
FROM prices 
JOIN
  (SELECT id, MIN(price1), quantity1 FROM prices GROUP BY id) AS t2 
ON t1.id=t2.id GROUP BY id