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

this is part 118 of sequel server
tutorial in this video we'll discuss
last value function in sequel server
this function is introduced in sequence
over 2012 it retrieves the last value
from the specified column with this
function order weight loss is required
partition by clause is optional rows or
range Clause is also optional but for
this last value function to work
correctly
we may have to explicitly specify a
value for this Clause here is the syntax
for this function first we specify the
name of the function and this function
has got one parameter the name of the
column from which we want to retrieve
the last value and then we use the over
Clause and within paranthesis we specify
order by 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 in this demo so within
our result set we want name gender
salary and let's use the last value
function so last underscore value this
function has got one parameter the name
of the column from which we want to
retrieve the last value so let's
retrieve the name of the employee and
then we will use the over clause within
paranthesis order by and then the column
width by which we want to sort the data
let's sort the data by salary column in
ascending order and let's give this
column an Ali ass let's call this last
value now what do we expect out of this
query notice we are sorting the data by
salary column in ascending order and
then we are asking for the name last
name so this query is going to give us
the name of the highest paid employee
and if you look at the data that we have
here the highest paid employee is Ron so
we expect
Ron's name to be displayed against every
employee row within the result set let's
execute this query and see if that's
what we get look at that we get totally
different result why is that that's
basically because notice within this
query we have not supplied an explicit
value for rows or range Clause since we
haven't specified an explicit value it's
using its default what is its default
value range between unbounded proceeding
and
sentro so this is what is causing you
know this result now let's understand
how actually this query retrieves you
know the names that we see here within
the last value column so when it's on
the first row so when this last value
function is being evaluated for the
first row you know this value for rules
or range Clause is in effect so what
does this mean range between unbounded
proceeding so what does unbounded
proceeding means it means the vendo for
last value function starts at the first
row so this is the first row so it
starts there and it ends at the current
row so it's the first row and it's the
current row so only that row is passed
to the last value function and then we
are asking for the name column value so
it retrieves mark and display it against
in our last value column when it comes
to the second row so when this function
is being evaluated for the second row so
unbounded proceeding means the window
starts at the first row so it's going to
pass mark row as well as John's row
because that's the current row out of
these two what is the last value John
and that's what is displayed right here
so it goes on like that when it comes to
Ron's record all the rules are passed
and all of this all rows Ron is the last
value and that's how it is displaying
Ron against this row in the last value
column so how to correct this to correct
this we will have to specify you know
what is the vendor going to be for our
last value function so I'm going to
change the value for rows or range
Clause to rows between unbounded
proceeding and unbounded following so
what does this mean unbounded proceeding
means the window for the last value
function starts at the first row within
the result set and unbounded following
means the window ends at the last row
within that result set so when it is on
the first row it is still going to pass
all these rows and that way we should
get the data that we expect so here I'm
going to specify an explicit value for
rows or range clause
so rows between unbounded
preceding and unbounded following so
let's execute this query and see what we
get so now notice we get the result that
we expect so Ron is the highest paid
employee and that's what we see against
every employee row now within this
example we don't have any partitions
right so we have the entire table here
now if we have partitions then the
highest paid employee from the
respective partition should be returned
so let's partition this data by gender
column when we do this we should get two
partitions male and female and from the
female employees partition we should get
the highest paid employee so within the
female employees partition notice that
Jody has the highest salary 8,000 and
that's what we see here and within male
employee partition drawn is the person
with the highest salary and that's what
we see as the value for male employee
partition so we have that same example
right here thank you for listening and
have a great day