SQL MAX command: MySQL on Maximum or highest value in a field

Some time we will be searching for the maximum value in a field of any MySql table. MAX() SQL command will return the record with maximum or highest value in the SQL table / column. Same way we can get the minimum value of a range of records by using SQL MIN command

SQL MAX for Highest record of the column with all other details

What happen if we apply MAX command to a non numeric field? We will get the record with highest alphabet. But the best way to use MAX command is to apply it in a numeric field.

id	name	class	mark
1	John Deo	Four	75
2	Max Ruin	Three	85
3	Arnold	Three	55
4	Krish Star	Four	60
5	John Mike	Four	60
6	Alex John	Four	55

We will apply the MAX command here like this to the field mark

SELECT max( mark ) FROM `student`

Related Tutorial Descending Order Top 3 records MySQL Min

max(mark)

The command collected the maximum value of the mark field and displayed. We can define some header like this also.


SELECT MAX(mark) as max_mark FROM `student`

max_mark
85

Using Subqueries

To display all fields like name, class, id along with the highest mark we can use like this. This will display one record with all details of the highest mark

SELECT id,name,class,MAX(mark) as max_mark FROM `student`

This will give wrong result.

As you can see below the above query will display Maximum mark but not the matching id, name, class. So we will try by using sub-query

id	name	class	max_mark
2	Max Ruin	Three	85

We can get matching output of id, name,class by using where clause by matching with maximum mark using subquery

SELECT * FROM `student` WHERE mark=(select max(mark) from student)

id	name	class	mark	sex
33	Kenn Rein	Six	96	female

Maximum mark in each class by using Group By

Now let us find out what is the maximum mark ( or highest ) in each class. Here we can use the Group By command to find out the maximum mark obtained by each class

SELECT class, max( mark ) as max_mark FROM
`student` GROUP BY class

class	max_mark
Four	75
Three	85

You can see above that maximum mark of each class is displayed. Since we have two class in our table so the sql command has returned two class with highest mark in each of them. We have to use Group By clause if we ask for the query to return any other field name other than the max. Otherwise system will generate error.

We can add condition to the sql command to get our desired result. We can add one Where clause to the query to consider records for a particular class only ( say Four)


SELECT max( mark ) as maximu_mark, class FROM student where class ='Four' GROUP BY class

max_mark	class
75	Four

When integer is stored in VARCHAR field

If numbers are stored in VARCHAR field then MAX command will return based on the first digit. Example.

SELECT MAX ( t1) from max_value

We stored these number in t1 ( VARCHAR ) column , 1,2,3,4,5,6,12,13

The output will be 6 ( Why not 13 ? )

We need to convert the data to integer first by using CONVERT function. Here is the correct query

SELECT MAX(CONVERT(t1,UNSIGNED))  FROM `max_value`

The output will be 13 .

To test the result here is the SQL dump of our max_value table.

CREATE TABLE IF NOT EXISTS `max_value` (
  `t1` varchar(2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
INSERT INTO `max_value` (`t1`) VALUES
('1'),
('2'),
('3'),
('4'),
('5'),
('6'),
('12'),
('13');

Getting Max from a date field

We can get Maximum value from a date field like this.

SELECT MAX(exam_dt) FROM student_mark

There are two exam dates available for each month. We may require the 2nd exam date for each month.

SELECT MAX(exam_dt) from student_mark group by month(exam_dt)

You can get SQL Dump of student_mark table here

Storing record with Maximum mark in new table

CREATE TABLE student_max SELECT * FROM `student` WHERE mark=(select max(mark) from student)

The new table student_max will retain the same table structure as student
If the table is already available, then use insert command.

INSERT INTO student_max SELECT * FROM `student` WHERE mark=(select max(mark) from student)

Using MAX Query in PHP Script

We can use above queries by using PHP Script. First we will connect to MySQL database.

Here is the sample code.

<?Php
require "config.php";

$count="SELECT class, max( mark ) as max_mark FROM `student` GROUP BY class";

echo "<table>";
echo "<tr><th>class</th><th>max_mark</th></tr>";
foreach ($dbo->query($count) as $row) {
echo "<tr ><td>$row[class]</td><td>$row[max_mark]</td></tr>";
}
echo "</table>";
?>

SQL Math References COUNT() Minimum value Query

alper	05-06-2009
very productive examples. it teaching GROUP BY statement by one example. thank you for your post.

David Koh	15-06-2009
Another way to get the whole row where max_mark is the highest. Instead of the below from the example: SELECT * FROM `student` WHERE mark=(select max(mark) from student) We can do: SELECT * FROM `student` ORDER BY max_mark DESC LIMIT 1 This might save db processing power instead of having 2 select statements

smo	17-06-2009
This is explained here in getting highest number by using limit

rammohan	26-06-2009
how to get the last entry in pages in a table

super	10-08-2009
What if you had two or three top students (say arnold got 85 as well), how would you display the name of all of them with there mark?

smo	11-08-2009
You can display top 3 by using order by and limit command. Here is the sql to display top three records.

arcavalierenero	12-09-2009
This sample isn't correct: SELECT id,name,class,MAX(mark) as max_mark FROM `student`

smo	12-09-2009
Yes, you are right. To display matching records we have to use subquery. The content is modified with the above explanation.

Doug M	15-09-2009
I got a complex array I need to build from SQL. Using a theory like this, how could I retrieve the top 3 scores per game from a highscores table? Got a mod I am working on where people are asking that. I am hoping there is a simple SQL solution rather than building a complex array.

smo	17-09-2009
you can use limit and order by to get top 3 records.

ajay	30-07-2009
how to get name of topper of every class

karthi	06-01-2010
how to display first 2 toppers in the class? any idea..

piyush	15-04-2010
I wanted to know how to find the max salary between two table pl tell me

Babs	18-05-2010
how do i pick the data with the highest currency from dis data? name itemdesc tamt smith longe 450 smith longe 470

rakesh7434	07-05-2010
i have a table with some data i need to reproduce with same data and add new cols to it how to do it please tell me

sai	01-07-2010
how to return a zero value if selected value of max(columnname) doesnot exist.

manmohan	29-07-2010
How to get result like this id name class mark 1 John Deo Four 75 2 Max Ruin Three 85 display max marks in each class with its other associated fields

dbl	03-08-2010
@manmohan something along the lines of select * from table group by class order by mark asc should do what you want

Atef	03-10-2010
how do i select the max ID value from table and insert this value in other ID record which in other table

anji	04-10-2010
I need max value ( in emp_city)in the below talbe emp_name Emp_id Emp_city abc 12 cc01 bbc 13 cc02 abc 12 cc01 agc 15 cc03 dbc 16 cc01 Result cc01 How can i get this one?please tell me anyone?

DARSHAN BHAWSAR	08-11-2010
I need an SQL to get the last updated transaction on the basis of date. eg. Date Remarks 13/09/10 a 13/10/10 b SQL should return value as b since it is last updated transaction.

satish.s	02-05-2011
Dear Sir/madam, We r dong project on vb with oledb conn with msaccess 2003 how to fetch maximum value of msaccess field to vb 6.0 please send us select statement of this query please do needfull Regards Satish.S

zorrs	20-05-2011
how to select the row/field base on the latest date

sadia	02-06-2011
i want to find sum of maximum marks of a student.e.g student got subject A = gpa 2.5 subject B= gpa 3 subject A= gpa 2 (reappeared) subjct A= gpa 3.5 (reappeared) sum of gpa= 3+3.5 what would be the query for it?

SQL MAX Command