SQL MAX Command

• Video Tutorial on MAX query

SELECT max( mark ) FROM `student`

SELECT MAX(mark) as max_mark FROM `student` 

Using Subqueries

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

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

Maximum mark in each class by using Group By

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

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

When integer is stored in VARCHAR field

SELECT MAX ( t1) from max_value

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

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

SELECT MAX(exam_dt) FROM student_mark

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)

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

Using MAX Query in PHP Script

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