SQL Server Portal

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 table
Create 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
(
Select
Row_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