Mysql查询查找最小和最多2列，其中最少1列不得等于零

Question

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,

Answer 1

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` )