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

SQL Server Function

User-Defined Functions are part of a of Database. User-Defined Functions accepts parameters (not mandatory),do work , and returns a type of result.The return may be integer,decimal,string even a table.

Syntax:

CREATE FUNCTION [dbo].[You Function Name]

(

@parameter DATATYPE

..

..

)

RETURNS DATATYPE

AS

BEGIN

RETURN Result

END

Function Call Syntax :

SELECT [dbo].[You Function Name]( @parameter DATATYPE .. ..)

Here is the example of function that calculate square of a number

 

CREATE FUNCTION dbo.fn_GetSquare

(

       @param1 int

)

RETURNS INT

AS

BEGIN

        RETURN @param1 * @param1

END

Here is the result.

SELECT dbo.fn_GetSquare(12) /* Result :144*/

SELECT dbo.fn_GetSquare(14) /* Result :196*/

SELECT dbo.fn_GetSquare(15) /* Result :225*/

SELECT dbo.fn_GetSquare(16) /* Result :256*/

Some Point on function
1)You can not use print statement in the function.

2)Function can be called from Stored Procedure, but Stored Procedure can not 

be called from functions.

3)You can not use temporary table in function.

4)Function can be called from Functions.

5)A function can return table.

Here , we have created two table and inserted data to a table for example purpose.

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',14,'36,student street','03/08/2018',11.5

UNION

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

UNION

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

UNION

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

UNION

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

UNION

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

UNION

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

UNION

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

UNION

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

Here is the example of function that return a table.

CREATE FUNCTION dbo.fn_GetStudentData()

     RETURNS @t TABLE

    (

           id INT NULL,

           name NVARCHAR(500) NOT NULL

    )

AS

BEGIN

     DECLARE @t1 TABLE

         (

           id INT ,

           name NVARCHAR(500)

          )

      INSERT INTO @t1 (id,name)

      SELECT id,name FROM student

      INSERT INTO @t(id,name)

      SELECT id,name FROM @t1

RETURN

END

Now we are calling the function.

SELECT * FROM dbo.fn_GetStudentData()

 

Output:
id   name
1    John10
1    John11
1    John2
1    John3
1    John4
1    John5
1    John6
1    John7
1    John8
1    John9
5    john25