Use MAX to get the largest value : Max « Math « SQL / MySQL






  1. SQL / MySQL
  2. Math
  3. Max

Use MAX to get the largest value

/*

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








Related examples in the same category

1.Simple MAX
2.Another MAX function