Row Number function in SQL Server Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 109 of signal server
tutorial in this video we'll discuss row
number function in sequel server this is
continuation to part 108 so please watch
part 108 before proceed in row number
function is introduced in sequel server
2005 it returns the sequential number of
arrow starting at one order by Clause is
required partition by Clause is optional
when the data is partitioned row number
is reset to 1 when the partition changes
we have the syntax here we first specify
the row number function followed by that
we use over keyword and then within
parenthesis 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 and that should make all these
points clear let's flip to sequel server
management studio we'll use this
employees table notice within our
results that at the moment we have got
three columns name gender salary in
addition to these three columns I also
want a row number against every row in
the result set
so I'm going to use sequel server row
underscore number function this function
does not take any parameters so followed
by the function name we use over keyword
and followed by that within parenthesis
we use order by Clause now let's sort
the data by gender column so order by
gender and let's give this column
Annalee as let's call this row number
let's execute this and look at that we
get a row number for every row in the
result set
starting from 1 now this order by Clause
is required if we don't specify the
order by clause we get an error saying
the function row number must have an
over clause with order by and the reason
it requires order by Clause is because
this row number is generated based on
the order that is imposed by this order
by Clause now at the moment we are not
using partition by let's see what's
going to happen when we use partition by
Clause
so let's partition the data by
gender column so we are partitioning the
data by gender column ordering it by
gender column so let's execute this
query and see what result we get look at
that when the data is partitioned look
at the first four rows first so that's
the female employees partition so the
row number has started at one and it
went all the way till four and here we
are changing the partition from female
to male
so at row number five look at what
happened to row number it is reset to
one and then it goes all the way until
sex
so whenever the partition changes row
number is reset to one so here is the
first example that we discussed without
partitioned row number goes from one all
the way till ten and here we have the
example of partitioning data and look at
the row number one two four four female
employees and one two six four male
employees now what is the use of this
row number function what's the use case
one of the excellent use cases is
deleting all duplicate rows except one
so if you have a situation where you
have a table with lot of duplicate rows
and you want to delete all those
duplicate rows except one then we can
use row number function to very easily
achieve that we discuss this in detail
in part four of sequence of interview
questions and answers video series so if
you go to sequence of interview
questions and answers video series and
then that part four is delete duplicate
rows in sequel and here is the
associated text article for that video
so let's actually quickly go over this
example so at the moment we already have
employees table so I'm going to delete
this employees table and use this script
to create that employees table once
again so let's copy the script and let's
delete this employees table first and
then we will recreate that employee's
table
so let's execute the script and let's
select the data from this table select
star from employees so notice we have
got you know several duplicate rows so
what we want to do is delete all the
duplicates except one and to do that we
are going to use this script let's
actually copy this okay so look at the
query here which is part of this CTE so
when we execute this look at what we get
so we get all the data in the employees
table Heidi first-name lastname gender
and salary columns and then we have a
row number and look at how we are using
row number function here so select star
this is going to review all the columns
from the employees table in addition to
all the columns we want row number so we
are using row number function followed
by that / keyword partition by ID column
so if you look at this ID column look at
that you know we have ID 1 and for ID 1
rows look at the row number 1 2 3 & 4
rows with ID - row number is 3 set to 1
and it goes from 1 to 2 and for this
partition where ID is 3 row number again
starts at 1 and goes all the way till 3
now so this is a CT a common table
expression on that CTE we are issuing a
delete query so the name of the CT is
employee CTE so delete from employee CTE
where row number is greater than 1 that
means it's going to delete wherever it
finds row number greater than 1 so it's
going to delete these two rows so what
happens when it deletes those two rows
we'll be left with one row where ID is
one similarly it's going to delete the
second row for the second partition and
for the third partition it is going to
delete these two rows
you know effectively at the end of it
it's going to leave just one row in each
partition now let's say for example you
want to delete all rows
except to duplicates then you can simply
change this condition to wear row number
greater than two so what is it going to
do in that case it's going to delete all
the rows where row number is greater
than 2 so from partition 1 it's going to
delete this row and from partition 2
it's not going to delete any row and
from partition 3 it's going to delete
the last row so let's actually execute
this query and see what we get
so it has deleted 2 rows from that table
so when we select from employees now
look at that
we have two duplicates okay for every
partition now when we change the
condition to where row number greater
than 1 it should delete all the
duplicates except one now let's select
the data and look at that so we don't
have duplicates anymore thank you for
listening and have a great day