Everyone i have stuck on strange problem in mysql. I am a newbie in mysql, hope someone can help me.
I have 2 columns of price in a table.mrp_price and discounted_price :
mrp_price discounted price
400 000
500 300
600 400
700 500
800 600
I want my mysql query to return minimum as 300 (whatever is minimum in mrp and discounted price but not to consider 000 values of dicounted price) and maximum as 800 (whatever is maximum in mrp_price and discounted_price).
my output will looks like this:
min(mrp_price) max(mrp_price) min(discounted_price) max(discounted_price)
400 800 300 600
I want my final output as :
min(price) max(price)
300 800
I want my final output with the help of query. I hop i am little bit clear now in making my frnds understand my problem.
Please help,
SELECT MAX(`p`)
, MIN(`p`)
FROM (SELECT Min(`discounted_price`) as `p` FROM `table` WHERE discounted_price!=0)
UNION
(SELECT Max(`mrp_price`) as `p` FROM `table` )
The logic is as follows : select two rows in one and then find maximum and minimum values.
SELECT MAX(`t`)
, MIN(`t`)
FROM ( SELECT `mrp_price` as `t` FROM `table`
UNION
SELECT `discounted price` as `t` FROM `table` )
WHERE `t` != 0
In case the first column is always greater than the second :
SELECT MAX(`mrp_price`)
, MIN(`discounted price`)
FROM `table`
WHERE `t` != 0
Your question is not quite clear. Do you want to select the mrp_price where discount is maximum or you want you want mrp_price where both mrp_price and discount is maximum. Same case for minimum. For the earlier case, you can try following query
SELECT MAX(mrp_price) FROM *{TABLE NAME}* WHERE discounted price=(MAX(discounted price))
Similar query for minimum
SELECT MIN(mrp_price) FROM *{TABLE NAME}* WHERE discounted price=(MIN(discounted price)) AND discounted price!=0
I would suggest this approach:
select least(min(case when discounted_price > 0 then discounted_price else 10000 end),
min(case when mrp_price > 0 then mrp_price else 10000 end)
)
from table t;