| | |
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`
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`
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
| 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)
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
| | 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 | | ajay | 30-07-2009 |
|---|
| how to get name of topper of every class | | 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. | | karthi | 06-01-2010 |
|---|
| how to display first 2 toppers in the class? any idea.. |
|
|
|
|
|
If you are facing any problem in viewing this page, please tell us
