MAX(): The maximum value in a set of values

Max Query
SELECT MAX(mark) FROM student
Some time we will be searching for the maximum value in a field of any MySql table. MAX() SQL command will return the highest value in the SQL table / column. Same way we can get the minimum value of a range of records by using SQL MIN command

SQL MAX for Highest record of the column with all other details

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`
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
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)
idnameclassmarksex
33Kenn ReinSix96female

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 particular class only  ( say Four)


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

MAX Query with JOIN

SELECT  a.store, MAX(qty) max_qty, b.product,
b.price * MAX(qty) AS total_price  FROM sales a
 LEFT JOIN products b ON a.p_id = b.p_id GROUP BY store;
storemax_qtyproducttotal_price
ABC3Monitor225
DEF2CPU110
Details of products and sales table with JOIN query

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)
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)
The new table student_max will retain the same table structure as student
If the table is already available, then use insert command.
INSERT INTO student_max SELECT * FROM `student` WHERE mark=(select max(mark) from student)

Using MAX Query in PHP Script

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

Understanding the SQL MAX Function in Data Analysis

The SQL MAX function plays a pivotal role in data analysis by enabling the extraction of the maximum value from a set of data within a database. This function is invaluable across numerous applications, from identifying peak sales periods in retail to determining the maximum values of sensor data in IoT applications. Role in Data Analysis
  • Performance Metrics: Analysts use the `MAX` function to gauge peak performance metrics, such as maximum sales in a month or the highest temperature recorded in a region.
  • Time Series Analysis: It is crucial for analyzing time-series data, helping to identify the points of maximum activity or interest over a specified period.
  • Comparative Analysis: In conjunction with other aggregate functions like `MIN`, `AVG`, and `SUM`, the `MAX` function offers a comprehensive view for comparative analysis across datasets.
Integration with Other SQL Features
  • GROUP BY Clause: Used together with the `GROUP BY` clause, `MAX` can reveal the maximum values for each group or category, providing detailed insights into each segment.
  • Window Functions: As a window function, `MAX` can assess data over a set of rows related to the current row, enabling complex analytical operations like moving maximum calculations.
  • JOIN Operations: When analyzing data from multiple tables, `MAX` can be used in conjunction with JOINs to find maximum values across related datasets.
Practical Examples
  • Business Intelligence: Identifying the maximum revenue generated by a product to assess its market success.
  • Healthcare Analysis: Evaluating the maximum patient wait times to improve healthcare delivery and patient satisfaction.
  • Environmental Monitoring: Assessing the maximum pollution levels to identify areas of concern and regulatory compliance.
Performance Considerations
  • Indexing: Proper indexing can enhance query performance, particularly for queries that frequently use the `MAX` function on large datasets.
  • Handling of NULL Values: It's important to understand that `MAX` ignores NULL values, which can affect the analysis if not accounted for in data preparation stages.

Conclusion

The SQL MAX function is essential for extracting the highest value from datasets, providing key insights into the upper bounds of data ranges. It facilitates a myriad of data analysis tasks, from simple maximum value identification to complex analytical queries involving multiple data points and categories. Integrating MAX with other SQL functions and clauses enables analysts to conduct in-depth, multifaceted analysis, making it a cornerstone function in the field of data analysis.


SQL Math References COUNT() Minimum value Query
Subscribe to our YouTube Channel here


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com
    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..
    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

    smo1234

    20-06-2016

    For this you need Order by Query
    jyoti

    13-07-2016

    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

    e.g.
    id name
    1 a
    2 b
    3 c
    4 a
    5 a

    so it should come "a"
    smo1234

    25-11-2018

    You can use ORDER BY with LIMIT query.
    Edward

    19-04-2019

    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

    Post your comments , suggestion , error , requirements etc here





    SQL Video Tutorials










    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer