SQL PHP HTML ASP JavaScript articles and free scripts to download

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

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)

Related Tutorial

• MySQL Min
• MySQL Avg
• MySQL Sum

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