Feeds:
Posts
Comments

Posts Tagged ‘Analytic Function – FIRST_VALUE’

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

minimum value in the group.1.1

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

minimum value in the group.1.1

Advertisements

Read Full Post »

In SQL server 2012, Microsoft has introduced some new analytical functions and one of the most important functions in it is FIRST_VALUE. As It name implies, it does the same.

Lets discuss this function syntax, purpose and examples in detail.

Syntax :

    FIRST_VALUE ( [scalar_expression )
    OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] )

Purpose :
It returns the first value from a list in a specified order (By default ascending order). Also we can do the partition in it but it is optional.

Lets create a student table and insert few records in it to demonstrate FIRST_VALUE.

Create Table [Student]
(
[Student ID] int,
[Student Name] varchar(50),
[Subject] varchar(50),
[Marks] int
)
GO

Insert into [Student] values (3,'Derek','English',100)
Insert into [Student] values (3,'Derek','Math',60)
Insert into [Student] values (3,'Derek','Science',90)

Insert into [Student] values (1,'Bob','English',80)
Insert into [Student] values (1,'Bob','Math',75)
Insert into [Student] values (1,'Bob','Science',60)
 
Insert into [Student] values (2,'Sandra','English',70)
Insert into [Student] values (2,'Sandra','Math',80)
Insert into [Student] values (2,'Sandra','Science',40)
Select * from [Student] Order By [Student ID]

Example-1 :
In this example, FIRST_VALUE returns the First Name of the student in the class (In ascending order) .

Select
 [Student ID]
,[Student Name]
,[Subject]
,[Marks]
,FIRST_Value([Student Name]) Over (Order By [Student Name]) as [First Value]
from dbo.[Student]
--Result

Example-2 :
In this example, FIRST_VALUE returns the student name having high score in the class.

Select
 [Student ID]
,[Student Name]
,[Subject]
,[Marks]
,FIRST_Value([Student Name]) Over (Order By [Marks] Desc) as [First Value]
from dbo.[Student]
--Result

Example-3 :
In this example, FIRST_VALUE returns the student name having high score in each subject in the class.

Select
 [Student ID]
,[Student Name]
,[Subject]
,[Marks]
,FIRST_Value([Student Name]) Over (Partition By [Subject] Order By [Marks] Desc) as [First Value]
from dbo.[Student]
--Result

Reference : MSDN

Read Full Post »