/*
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.01 sec)
mysql> /* The Row Holding the Maximum of a Certain Column */
mysql> SELECT article, dealer, price
-> FROM report
-> WHERE price=(SELECT MAX(price) FROM report);
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 0004 | D | 21.29 |
+---------+--------+-------+
1 row 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;
/* The Row Holding the Maximum of a Certain Column */
SELECT article, dealer, price
FROM report
WHERE price=(SELECT MAX(price) FROM report);
|