FIRST VALUE function in SQL Server Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 115 of sequence of Vitoria
in this video well discuss the first
value function in sequel server this
function is introduced in sequel server
2012 it retrieves the first value from
the specified column with this function
order by Clause is required partition by
Clause is optional we have this in tax
here first we specify the name of the
function this function has got 1
parameter the name of the column from
which we want to retrieve the first
value followed by the ad we use the
overt keyword and then within
parentheses we use the order by clause
and then the column list by which we
want to sort the data let's look at an
example we'll use this employees table
for the examples in this demo from this
table we want to retrieve name gender
salary and then we are going to use the
first value function this function has
got one parameter the name of the column
from which we want to retrieve the first
value so let's retrieve the name of the
employee so I'm going to specify the
column name as name and then we use the
over keyword and then within parentheses
we use auto by let's order the data by
salary column and let's give this column
an Ali ass let's call this first value
so this order by Clause is going to sort
this data in ascending order of salary
so that means the first row will be the
row of the employee with the lowest
salary and then we are asking the name
of such an employee so let's retrieve
this and see what we get first of all
notice the salary is arranged in
ascending order and from this sorted
data the first row is marks row and then
we are using this first value function
on the name column so it's going to
retrieve mark and return that for all
the rows within this result set at the
moment we are not using any partitions
let's see what's going to happen when we
use partition by clause so let's
partition the data by gender column
let's execute this query and see what we
get first of all notice that the
data is divided into two partitions
female and male partitions and look at
that within female partition the lowest
paid employee is spam and that's what we
get across every row in the female
employee partition whereas within the
male employee partition mark is the one
with the lowest paid salary and that's
what we get across every row within male
employee partition we have our second
example here thank you for listening and
have a great day