Serial Number using ROW_NUMBER (Transact-SQL)

Serial No is possible for your table using ROW_NUMBER


Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

Check out this Example

create table myTable
intNo int,
fname varchar(200),
mark numeric(5,2)

insert into mytable values(1,'xyz',89.45)
insert into mytable values(2,'xyz1',93.45)
insert into mytable values(3,'xyz2',35.45)
insert into mytable values(4,'xyz3',88.45)
insert into mytable values(5,'xyz4',73.45)

select ROW_NUMBER()OVER(ORDER BY mark DESC) AS 'Row Number' ,intNo,
fname,mark from MyTable


Note:it will run in sql server 2005 and above...

No comments: