/*
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;