Serial Number using ROW_NUMBER (Transact-SQL)

Serial No is possible for your table using ROW_NUMBER


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

output



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