SQL PHP HTML ASP JavaScript articles and free scripts to download
SQL Math Functions

SQL MAX Command

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

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`

max(mark)
85
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`

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
idnameclassmax_mark
2Max RuinThree85
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)

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

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>";
?>

Number of User Comments : 32


Google+

alper05-06-2009
very productive examples. it teaching GROUP BY statement by one example. thank you for your post.
David Koh15-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
smo17-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
ajay30-07-2009
how to get name of topper of every class
super10-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?
smo11-08-2009
You can display top 3 by using order by and limit command. Here is the sql to display top three records.
arcavalierenero12-09-2009
This sample isn't correct:
SELECT id,name,class,MAX(mark) as max_mark FROM `student`
smo12-09-2009
Yes, you are right. To display matching records we have to use subquery. The content is modified with the above explanation.
Doug M15-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.
smo17-09-2009
you can use limit and order by to get top 3 records.
karthi06-01-2010
how to display first 2 toppers in the class? any idea..
piyush15-04-2010
I wanted to know how to find the max salary between two table pl tell me
rakesh743407-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
Babs18-05-2010
how do i pick the data with the highest currency from dis data?

name itemdesc tamt
smith longe 450
smith longe 470
sai01-07-2010
how to return a zero value if selected value of max(columnname) doesnot exist.
manmohan29-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
dbl03-08-2010
@manmohan something along the lines of

select * from table group by class order by mark asc

should do what you want
Atef03-10-2010
how do i select the max ID value from table and insert this value in other ID record which in other table
anji04-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 BHAWSAR08-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.s02-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
zorrs20-05-2011
how to select the row/field base on the latest date
sanzoO07-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...
some12die417-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
Bim17-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;
vinoth12-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 ballada08-11-2012
how to get the largest average balance in Sql....can you help?
vishakha gupta16-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.
Sirjiskit06-05-2014
How will I find the position of students in examination from the average
Vijay Sharma07-07-2014
hello,tell me that how i fine the second largest salary in a emp table.
vijay23-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?
Post Comment This is for short comments only. Use the forum for more discussions.
Name
Email( not to be displayed)Privacy Policy
1+2=This is to prevent automatic submission by spammers. Please enter the result of the sum as asked




HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us

©2000-2014 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer