RowNumbering & Partitioning in SQL Server 2005.

RowNumbering & Partitioning in SQL Server 2005.

Suppose there is a table named Book having columns
BookId,
AuthorName,
BookTitle

Suppose you want to create a column with a serial number then the RowNumber() function is a right candidate.

Create Table #t1
(
BookId int,
AuthorName varchar(50),
TitleName varchar(50)
)

Insert into #t1 Values (100, 'A','A Book of A')
Insert into #t1 Values (101, 'A','B Book of A')
Insert into #t1 Values (102, 'B','A Book of B')
Insert into #t1 Values (103, 'B','B Book of B')
Insert into #t1 Values (104, 'B','C Book of B')
Insert into #t1 Values (105, 'C','A Book of C')
Insert into #t1 Values (105, 'C','B Book of C')
Insert into #t1 Values (105, 'C','C Book of C')
Insert into #t1 Values (105, 'C','D Book of C')

-- Usage of RowNumber() functon.
Select ROW_NUMBER()Over( order by BookId) as RowNumber,* from #t1
1 100 A A Book of A
2 101 A B Book of A
3 102 B A Book of B
4 103 B B Book of B
5 104 B C Book of B
6 105 C A Book of C
7 105 C B Book of C
8 105 C C Book of C
9 105 C D Book of C
This will add one column which will be a RowNumber.

--Usage of Partitioning

Select ROW_NUMBER()Over(Partition By AuthorName order by BookId) as RowNumber,* from #t1

1 100 A A Book of A
2 101 A B Book of A
1 102 B A Book of B
2 103 B B Book of B
3 104 B C Book of B
1 105 C A Book of C
2 105 C B Book of C
3 105 C C Book of C
4 105 C D Book of C


Ref http://www.sqlservercentral.com/articles/T-SQL/70264/