How to select minimum value from group in a table

Sometimes, you need to define window frame within the table on the basis of certain criteria, to pick up some specific data. Today, I was developing one report for my customer and the requirement was very simple, to find the people with minimum wage in each department. We do have the solution using RANK(ROW_NUMBER) function. However, we can achieve it using FIRST_VALUE function (Shipped with SQL Server 2012) as well.

Let me create a sample to demonstrate the script.

--This script is compatible with SQL Server 2005 and above.
--Create tableCreate Table [Employee]([Employee ID] int,[Employee Name] varchar(50),[Department] varchar(50),[Salary] int) 
GO
--Insert records into table
Insert Into [Employee] Values (1,'Ali','IT',10000)
Insert Into [Employee] Values (2,'Derek','IT',6000)
Insert Into [Employee] Values (3,'Jack','IT',9000)
Insert Into [Employee] Values (4,'Simon','Marketing',5000)
Insert Into [Employee] Values (5,'Monica','Marketing',7500)Insert 
Into [Employee] Values (6,'Sandra','Marketing',8000)
GO

Given below are the solutions :

SOLUTION 1 :
This is an old method and we have been using it since SQL Server 2005. We use a Ranking Function namely ROW_NUMBER in this solution to find the minimum value in the group. Given below is the script.

--This script is compatible with SQL Server 2005 and above.
;With CTE As(SelectRow_Number() Over (Partition By Department Order By Salary) As [S.No],[Employee ID],[Employee Name],[Department],[Salary]
From dbo.[Employee])
Select[Employee ID],[Employee Name],[Department],[Salary]
from CTE
Where [S.No]=1--OUTPUT

SOLUTION 2 :
We will use a new analytic function namely FIRST_VALUE in this solution to find the minimum value in the group. Given below is the script.

--This script is compatible with SQL Server 2012 and above.
;WITH CTE As(Select[Employee ID],[Employee Name],[Department],[Salary],FIRST_Value([Salary]) Over(Partition By [Department] Order By [Salary]) As [minimum Value]
From dbo.[Employee])
Select[Employee ID],[Employee Name],[Department],[Salary]From CTE
Where [Salary]=[minimum Value]
GO
--OUTPUT

Leave a Reply

Your email address will not be published. Required fields are marked *