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. Same way we can get the minimum value of a range of records by using SQL MIN command
Video Tutorial on MAX query
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
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 perticular 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)
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
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.
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
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
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
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?
sanzoO
07-10-2011
There is table say tbl_data which have the folling data id, user_name, mark1, mark2, mark3, mark4 ......
what ll be the query for finding the name of 3rd heighest scrorer
Help me out...
some12die4
17-02-2012
Anoher simple way is just to use order and limit like this:
SELECT xx,xx2 FROM students ORDER BY mark DESC LIMIT 1
Bim
17-02-2012
@sanzoO
select * from `tbl_data` ORDER BY `mark1`+`mark2`+`mark3`+`mark4` DESC LIMIT 2,1;
@zorrs & @DARSHAN BHAWSAR
have to be more specific, group by `id` ORDER BY max(`latestdate`) DESC ?
@anji
SELECT * FROM `table` ORDER BY `Emp_id` DESC LIMIT 1;
vinoth
12-09-2012
i have tried to be in inner join Based,Find Max Value
The Query Is:
select Last(b.ProgramName),RDate,ValidDate, OfferOpenDate,Last(c.OfferID) from tblStudentReg as a left outer join (tblOfferProgram as b left outer join tblOfferScheme as c on b.OfferID=c.OfferID)on a.ProgramName=b.ProgramName where RegistrationNO=1 group by RDate,ValidDate,OfferOpenDate
arlene ballada
08-11-2012
how to get the largest average balance in Sql....can you help?
vishakha gupta
16-04-2014
Hi, i wanna know that on selecting any value in my form then retreving the data only some value from my table within my database on the basis of matching the starting string i.e. "CA-1" in php then how can i do this?
please ans.
Sirjiskit
06-05-2014
How will I find the position of students in examination from the average
Vijay Sharma
07-07-2014
hello,tell me that how i fine the second largest salary in a emp table.
vijay
23-08-2014
i have a table that table name is student
how i get max(mark) in max(Id) and min(mark) in min(Id) in singl tbl?
rakesh chand
30-01-2015
very very Thank you for this tutorial.
prameela
16-03-2015
we have a two tables mid1,mid2, by using that we sholud get the best of the two.
Ujjwal Kumar
09-11-2015
I am trying the fetch the biggest value from any where in database table sql query but I am unable to do so please if anybody know please share query???
L.Sankaranarayanan
14-11-2015
SELECT id,name,class,MAX(mark) as max_mark FROM `student` didn't work in my h2 An error message showing 'group by ' necessary after stuent
Deepak
06-06-2016
level_type = 1;
i want to get all maximum maximum value where level_type='1';
like a example
user1=50;
user2 = 80;
user3 = 100;
user4=10;
then result should be = 100,80,50,10.. thanks in advance ...sorry for weak english
I am trying the fetch the biggest value from any where in database table sql query but I am unable to do so please if anybody know please share query?.......
khenu
08-04-2017
i am trying to get the maximum value for this list of numbers 0,1,2,3,4,5,10,12,13,14,15 using SELECT MAX(column-name)
table-name would give a result of 5 and not 15. Why?
smo1234
10-04-2017
It is considering the data as chars and first character is used for ranking, Now the highest first char is 5 , so it is returning 5 as maximum value. You need to convert the data to integer first and then apply MAX query. This part is added now. If all your data is integer then you can change the column type to Integer and they try MAX command
yagnesh patel
23-11-2018
I want to result in mysql query as given
max times stored value in table
Very useful article....its has explained very clearly why data was not returning properly from my query as one of the number fields in my database column was formatted a VARCHAR,therefore trying to return a max() on this field was returning the wrong figure. The explanation offered is brilliant ! Thank you