Mega Code Archive

 

Categories / MySQL / Select Clause

 

Use defined variable in new select clause

/* mysql> SELECT * FROM report; +---------+--------+-------+ | article | dealer | price | +---------+--------+-------+ |    0001 | A      |  4.45 | |    0001 | B      |  5.45 | |    0002 | A      | 16.67 | |    0003 | B      |  6.12 | |    0003 | C      |  2.78 | |    0003 | D      |  2.34 | |    0004 | D      | 21.29 | +---------+--------+-------+ 7 rows in set (0.00 sec) mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM report; +------------------------+------------------------+ | @min_price:=MIN(price) | @max_price:=MAX(price) | +------------------------+------------------------+ |                   2.34 |                  21.29 | +------------------------+------------------------+ 1 row in set (0.00 sec) mysql> /* Using User Variables */ mysql> SELECT * FROM report WHERE price=@min_price OR price=@max_price; +---------+--------+-------+ | article | dealer | price | +---------+--------+-------+ |    0003 | D      |  2.34 | |    0004 | D      | 21.29 | +---------+--------+-------+ 2 rows in set (0.00 sec) */ Drop table report;   CREATE TABLE report (        article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,        dealer  CHAR(20)                 DEFAULT ''     NOT NULL,        price   DOUBLE(16,2)             DEFAULT '0.00' NOT NULL,        PRIMARY KEY(article, dealer)); INSERT INTO report VALUES (1,'A',4.45),                         (1,'B',5.45),                         (2,'A',16.67),                         (3,'B',6.12),                         (3,'C',2.78),                         (3,'D',2.34),                         (4,'D',21.29);      SELECT * FROM report;     SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM report;    /* Using User Variables */ SELECT * FROM report WHERE price=@min_price OR price=@max_price;