number

Row Number function in SQL Server



Sharing buttons:

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