When we need to find the maximum number in a column of our database, the MAX function is used. When we have requirement to get the highest paid employee in a company, the highest marks scored by a student, maximum hike given, etc..., the MAX function comes handy. Consider the syntax below which returns the maximum score from the table tblCricket:

SELECT MAX(score) FROM tblCricket;

The MAX() function can be added to the subquery along with the WHERE clause (as shown below). The subquery returns the highest score in a match. The outer query lists out these contents of the table with score that are less than of the maximum value.

SELECT * FROM tblcricket

WHERE score < (

   SELECT MAX(score) FROM tblcricket

);

The MAX() function can as well be combined with the ORDER BY clause and the GROUP BY clause to get the maximum number for the particular data. In the example below, the player's name and the maximum score hit by the player in his career are displayed. The data is, then, grouped by the player's name.

SELECT player, MAX(score) from tblCricket>

GROUP BY player 

ORDER BY MAX(score);

The MAX() function can be combined with the HAVING clause and the GROUP BY clause to add filter to the data. In the example below, the player name's and the maximum score hit by the player in his career are displayed where score is greater than 100 only. The data is grouped by player name.

SELECT player, MAX(score) from tblCricket

GROUP BY player

HAVING MAX(score) > 100;