Fetch maximum individual marks for a student with marks1 and marks2 records in MySQL?

For this, use MAX() along with GROUP BY clause. Let us first create a table −

mysql> create table DemoTable
   -> (
   -> StudentEmailId varchar(20),
   -> Marks1 int,
   -> Marks2 int
-> );
Query OK, 0 rows affected (0.90 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable values('John@gmail.com',45,32);
Query OK, 1 row affected (0.30 sec)
mysql> insert into DemoTable values('John@gmail.com',32,45);
Query OK, 1 row affected (0.34 sec)
mysql> insert into DemoTable values('Carol@gmail.com',32,45);
Query OK, 1 row affected (1.64 sec)
mysql> insert into DemoTable values('David@gmail.com',45,32);
Query OK, 1 row affected (0.18 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable ;

This will produce the following output −

+-----------------+--------+--------+
| StudentEmailId  | Marks1 | Marks2 |
+-----------------+--------+--------+
| John@gmail.com |      45 |     32 |
| John@gmail.com |      32 |     45 |
| Carol@gmail.com |     32 |     45 |
| David@gmail.com |     45 |     32 |
+-----------------+--------+--------+
4 rows in set (0.00 sec)

Here is the query to fetch maximum individual marks for a student with marks1 and marks2 records −

mysql> select StudentEmailId,max(Marks1),max(Marks2) from DemoTable
   -> group by StudentEmailId;

This will produce the following output −

+-----------------+-------------+-------------+
| StudentEmailId  | max(Marks1) | max(Marks2) |
+-----------------+-------------+-------------+
| John@gmail.com  |          45 |          45 |
| Carol@gmail.com |          32 |          45 |
| David@gmail.com |          45 |          32 |
+-----------------+-------------+-------------+
3 rows in set (0.00 sec)

AmitDiwan

Published on 18-Dec-2019 05:05:52

• Related Questions & Answers
• Get maximum age from records with similar student names in MySQL
• Group the marks of a particular student from a table and display total marks in a separate column for each student?
• MySQL query for text search with LIKE and OR to fetch records
• How to update document with marks value in MongoDB for student David
• Implement MySQL REGEXP to fetch records with . and numbers
• Set custom messages on the basis of a column with student marks in MySQL
• Fetch student records whose result declared 12 days before the current date in MYSQL
• MySQL query to find a match and fetch records
• MySQL to fetch records based on a specific month and year?
• Display the student marks in a single column on the basis of subject in MySQL?
• Fetch records containing a specific character twice in MySQL
• Fetch ordered records after a specific limit in MySQL
• MySQL RegExp to fetch records with only a specific number of words
• How to order records and fetch some using MySQL LIMIT?
• How can fetch records from specific month and year in a MySQL table?