CODEWITHSUNDEEP
mysql
mediaroot
mediaroot

Reputation: 445

How to check if value is bigger than each other value?

I need to check if one value is bigger than each of other values.

Currently I have this query but it only check if the value is bigger than all of total other values:

IF (SELECT SUM(`price`) AS '14' 
    from data 
    where sale = 14) > (
        SELECT SUM(`price`) AS 'x14' 
        from data where sale != 14)
THEN SET New.price=1.99;

While I need it to check if sale's(14)-related is bigger than (15) and bigger than (16)..

Upvotes: 1

Views: 642

Answers (1)

Riad
Riad

Reputation: 3850

//DECLARE  price_1 and 2 first 

SELECT SUM(`price`) INTO price_1  from data where sale = 14 ;
SELECT MAX(price_others) INTO max_price FROM 
( 
  SELECT SUM(`price`) as price_others, sale  FROM data 
  where sale != 14 GROUP BY sale 
) TMP ;

IF price_1 > max_price THEN 
  SET New.price=1.99;
END IF ;

Upvotes: 1

Related Questions