Monday, August 28, 2006

T-SQL ROW_NUMBER() function

Number you eggs before they hatch! :)) Bad one!!


I search a lot about giving row number to each resultant row of select statement but it seemed such automatic row numbering function is not available in T-SQL (SQL which runs with MS SQL server). But my colleague, Subamanin, gave a wonderful solution which I want share with you. Code goes like this notice the ROW_NUMBER() function.


CODE:

declare @EMP table
(
emp_name VARCHAR(10),
emp_post
varchar(10)
)


insert into @EMP values('A11','00A')
insert
into @EMP values('A12','00B')
insert
into @EMP values('A13','00C')
insert
into @EMP values('A14','00A')
insert
into @EMP values('A14','00A')

select *
from @EMP --Produces Table(0)

select *,
ROW_NUMBER
() over(order by emp_name) as ID_NUM
from
@EMP
--Produces Table(1)


And this is RESULT:

TABLE(0)

emp_name

emp_post

A11

00A

A12

00B

A13

00C

A14

00A

A14

00A


TABLE(1)

emp_name

emp_post

ID_NUM

A11

00A

1

A12

00B

2

A13

00C

3

A14

00A

4

A14

00A

5

But unfortunately over(order by emp_name) function has to be there. Here in 'over' function you can order by column of your choice which may or may not be your primary or unique key.