Calculate the maximum value by using the MAX() function: MAX() : MAX « Aggregate Functions « SQL / MySQL






  1. SQL / MySQL
  2. Aggregate Functions
  3. MAX

Calculate the maximum value by using the MAX() function: MAX()

      
mysql>
mysql> CREATE TABLE Classes
    -> (
    ->     ClassID SMALLINT NOT NULL PRIMARY KEY,
    ->     Dept CHAR(4) NOT NULL,
    ->     Level ENUM('Upper', 'Lower') NOT NULL,
    ->     TotalStudents TINYINT UNSIGNED NOT NULL
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO Classes VALUES
    -> (1001, 'ANTH', 'Upper', 25),
    -> (1002, 'ANTH', 'Upper', 25),
    -> (1003, 'MATH', 'Upper', 18),
    -> (1004, 'ANTH', 'Lower', 19),
    -> (1005, 'ENGL', 'Upper', 28),
    -> (1006, 'MATH', 'Lower', 23),
    -> (1007, 'ENGL', 'Upper', 25),
    -> (1008, 'MATH', 'Lower', 29),
    -> (1009, 'ANTH', 'Upper', 25),
    -> (1010, 'ANTH', 'Lower', 30),
    -> (1011, 'ENGL', 'Lower', 26),
    -> (1012, 'MATH', 'Lower', 22),
    -> (1013, 'ANTH', 'Upper', 27),
    -> (1014, 'ANTH', 'Upper', 21),
    -> (1015, 'ENGL', 'Lower', 25),
    -> (1016, 'ENGL', 'Upper', 32);
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql> SELECT Dept, Level, MAX(TotalStudents) AS Maximum
    -> FROM Classes
    -> GROUP BY Dept, Level WITH ROLLUP;
+------+-------+---------+
| Dept | Level | Maximum |
+------+-------+---------+
| ANTH | Upper |      27 |
| ANTH | Lower |      30 |
| ANTH | NULL  |      30 |
| ENGL | Upper |      32 |
| ENGL | Lower |      26 |
| ENGL | NULL  |      32 |
| MATH | Upper |      18 |
| MATH | Lower |      29 |
| MATH | NULL  |      29 |
| NULL | NULL  |      32 |
+------+-------+---------+
10 rows in set (0.00 sec)

mysql>
mysql>
mysql> drop table Classes;
Query OK, 0 rows affected (0.00 sec)








Related examples in the same category

1.Get the maximum price and minimum price
2.Summarizing with MIN( ) and MAX( )
3.What are the first and last state names, lexically speaking?
4.Use the CONCAT( ) expression with MAX( ) to find the value with the largest population part:
5.Finding Values Associated with Minimum and Maximum Values
6.Determine the population range:
7.To obtain the state name associated with the maximum population
8.Returning the Maximum Value with MAX()
9.Finds the size of the largest message sent between each pair of sender and recipient values listed