sql - Get results only with max change? -

i need return results have highest percentage of change. table looks like

item      price1    price2         1         2 b         3         5 c         2         3

i want return a, this:

item      price1    price2   percent_change         1         2        200%

i tried

   select item, price1, price2, max(a.mprice) my_output (select item,    ((price2-price1)/price1) * 100 mprice table)

but says indetifier invalid (assuming that's a.mprice). i've tried using having check max, didn't work, still return results.

i feel i'm missing obvious can't think of is. i'm guessing using max(a.mprice) wouldn't max need, i'm not sure else do.

your table a not have item , mprice fetching price1 , price2 in outer query not work.

but can without such subquery

select item, price1, price2, ((price2-price1)/price1)*100 growth products order growth desc  limit 1;

first, choose 3 attributes , calculate growth, order growth descending , take top value.

XPATH