Get maximum age from records with similar student names in MySQL
For this, you can use GROUP BY along with aggregate function MAX(). Let us first create a table −
mysql> create table DemoTable1964
(
StudentName varchar(20),
StudentAge int
);
Query OK, 0 rows affected (0.00 sec)
Insert some records in the table using insert command −
mysql> insert into DemoTable1964 values('Chris',23);
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1964 values('David',34);
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1964 values('Chris',27);
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1964 values('Sam',31);
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1964 values('David',32);
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1964 values('David',37);
Query OK, 1 row affected (0.00 sec)Display all records from the table using select statement −
mysql> select * from DemoTable1964;
This will produce the following output −
+-------------+------------+
| StudentName | StudentAge |
+-------------+------------+
| Chris | 23 |
| David | 34 |
| Chris | 27 |
| Sam | 31 |
| David | 32 |
| David | 37 |
+-------------+------------+
6 rows in set (0.00 sec)
Here is the query to get maximum age:
mysql> select StudentName,max(StudentAge) from DemoTable1964 group by StudentName;
This will produce the following output −
+-------------+-----------------+
| StudentName | max(StudentAge) |
+-------------+-----------------+
| Chris | 27 |
| David | 37 |
| Sam | 31 |
+-------------+-----------------+
3 rows in set (0.00 sec)
Published on 31-Dec-2019 12:01:10
