SQL Server MAX

MAX  functions  the highest of the data of a particular column . The data maybe integer , numeric decimal ,date, character type. Max  return the highest alphabetical value when applied on character set.We have created a table and corresponding data to do some sample example.

CREATE TABLE student

(

         id             INT NOT NULL,

         name           VARCHAR (500) NOT NULL,

         age            INT NOT NULL,

         adress         VARCHAR (500) NOT NULL,

         date_of_birth  DATETIME NOT NULL,

         height         DECIMAL(10,2) NOT NULL

)

INSERT INTO student(id,name,age,adress,date_of_birth,height)

SELECT 1,'John1',12,'34,student street','01/08/2018',10.5

UNION

SELECT 1,'John2',13,'35,student street','02/08/2018',11

UNION

SELECT 1,'John3',12,'36,student street','03/08/2018',11.5

UNION

SELECT 1,'John4',13,'37,student street','04/08/2018',12

UNION

SELECT 1,'John5',12,'38,student street','05/08/2018',12.5

UNION

SELECT 1,'John6',13,'39,student street','06/08/2018',13.5

UNION

SELECT 1,'John7',15,'40,student street','07/08/2018',14

UNION

SELECT 1,'John8',14,'41,student street','08/08/2018',14.5

UNION

SELECT 1,'John9',12,'42,student street','09/08/2018',15

UNION

SELECT 1,'John10',15,'43,student street','10/08/2018',15.5

UNION

SELECT 1,'John11',16,'44,student street','11/08/2018',16

Example 1
Here is example of MAX applied on a integer field. The example return the  the highest value of the   column applied.

SELECT MAX(age) FROM student

Result :16

Example 2

Here is the example of MAX applied on a character field. The highest alphabetical value will be  return.

SELECT MAX(name) FROM student

Result :John9

Max discard null. It does not work on null.It work on DISTINCT,All,group by, order by partition over etc.
 
Example 3
Here is the example of MAX applied with Distinct.  

SELECT MAX(DISTINCT height) FROM student

Result :16

Example 4

Here is the example of Max with group by.

SELECT age as age_group,MAX(height)

                    FROM student

                    GROUP BY age

Result :

age_group height

12        15.00
13        13.50
14        14.50
15        15.50
16        16.00

Example 4
Here is the example of partition over with max. Here Max can be used over each partition.
 

SELECT DISTINCT age as age_group,MAX(height) OVER (PARTITION BY age) AS height

FROM student

Result :

age_group height

12        15.00
13        13.50
14        14.50
15        15.50
16        16.00

Here is the example of MAX on a date field.

SELECT MAX(date_of_birth) FROM student

Result :2018-01-08 00:00:00.000