Sunday, August 4, 2013

First_Value and Last_Value

Overview

SQL Server 2012 introduces following analytical functions
  • CUM_DIST & Percentile_DIST
  • FIRST_Value & Last_Value
  • LAG & LEAD
  • Percent_Count & Percent_Range
First_Value & Last_Value functions allow you to get the same value for the first and last row for all records in a result set, it will also be useful in a scenario where you want to compare particular thing with max and minimum value of the same thing.

Syntax

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

Scenarios & Example

  • Find out highest and lowest value for particular resultset

Let's say we have a table called "Marks" which contain the marks of each student, each subject. and we want to compare marks of particular subject for each student with highest and lowest marks then this is how we can get it,

SELECT StudentName, Marks, FIRST_VALUE (Marks) OVER (ORDER BY Marks) LowestMarks
, LAST_VALUE (Marks) OVER (ORDER BY Marks) HighestMarksSoFar
, LAST_VALUE (Marks) OVER (ORDER BY Marks ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) HighestMarks
FROM   Marks
WHERE  SubjectName = 'Maths'


StudentName
Marks
LowestMarks
HighestMarksSoFar
HighestMarks
Rob
67
67
67
100
Ryan
97
67
97
100
Bob
98
67
98
100
John
100
67
100
100


We can see that, when we use "Rows Between Unbound Preceding and Unbound Following" in LAST_Value function, then we are getting actual Highest value from that result-set otherwise it gives the highest value whatever we have got so far. This is NOT the case for First_Value function

  • Find out highest and lowest value using Partition By clause

Let's say we want to get above result for all the subjects then in this case, we need to use Partition By clause.

SELECT SubjectName, StudentName, Marks, FIRST_VALUE (Marks) OVER (PARTITION BY SubjectName ORDER BY Marks) LowestMarks
, LAST_VALUE (Marks) OVER (PARTITION BY SubjectName ORDER BY Marks ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) HighestMarks
FROM   Marks
order by 1,3


SubjectName
StudentName
Marks
LowestMarks
HighestMarks
English
Ryan
89
89
99
English
Bob
93
89
99
English
John
94
89
99
English
Rob
99
89
99
Maths
Rob
67
67
100
Maths
Ryan
97
67
100
Maths
Bob
98
67
100
Maths
John
100
67
100
Science
Rob
87
87
98
Science
Ryan
94
87
98
Science
John
98
87
98
Science
Bob
98
87
98




No comments:

Post a Comment