ROW_NUMBER SQL Server Example

SQL Server > Built-in Functions >  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.

Example:

CREATE TABLE #LocalTempTable(

       ID            int,
       Name   varchar(50),
       Salary int,
       DeptId int)

insert into #LocalTempTable(Id, Name, Salary, DeptId) values (1,'p1',100,1),  (2,'p2',200,1) ,  (3,'p2', 500,2)

SELECT ROW_NUMBER() OVER(ORDER BY Id DESC) AS Row,

    Name, Salary
FROM
       #LocalTempTable;

-- specify number of rows

WITH tbl AS
(
    SELECT Id, Name,
    ROW_NUMBER() OVER (ORDER BY Salary) AS RowNumber
    FROM #LocalTempTable
)

SELECT Id, Name, RowNumber 
FROM tbl
WHERE RowNumber BETWEEN 1 AND 2;

drop table #LocalTempTable