SQL Server/T-SQL Tutorial/Analytical Functions/ROW NUMBER

ROW_NUMBER function returns the sequential number of a row within a result set, starting at 1 for the first row.

SQL Server 2005"s new ROW_NUMBER function returns an incrementing integer for each row in a set.
The syntax for ROW_NUMBER is as follows:
ROW_NUMBER ( )     OVER ( [ <partition_by_clause> ] <order_by_clause> )
partition_by_clause allows you to restart row numbering for each change in the partitioned column. order_by_clause
 determines the order in which the ROW_NUMBER is applied to the results.

8> create table department(
9>    dept_name     char(20)     not null,
10>    emp_cnt       int          not null,
11>    budget        float,
12>    date_month    datetime);
13> GO
1>
2> insert into department values("Research", 5, 50000, "01.01.2002");
3> insert into department values("Research", 10, 70000, "01.02.2002");
4> insert into department values("Research", 5, 65000, "01.07.2002");
5> insert into department values("Accounting", 5, 10000, "01.07.2002");
6> insert into department values("Accounting", 10, 40000, "01.02.2002");
7> insert into department values("Accounting", 6, 30000, "01.01.2002");
8> insert into department values("Accounting", 6, 40000, "01.02.2003");
9> insert into department values("Marketing", 6, 10000, "01.01.2003");
10> insert into department values("Marketing", 10, 40000, "01.02.2003");
11> insert into department values("Marketing", 3, 30000, "01.07.2003");
12> insert into department values("Marketing", 5, 40000, "01.01.2003");
13> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2> SELECT dept_name, emp_cnt, CAST(budget AS INT) AS budget, date_month,
3>          RANK() OVER(PARTITION BY date_month ORDER BY emp_cnt desc) AS rank
4> FROM department;
5> GO
dept_name            emp_cnt     budget      date_month              rank
-------------------- ----------- ----------- ----------------------- --------------------
Accounting                     6       30000 2002-01-01 00:00:00.000                    1
Research                       5       50000 2002-01-01 00:00:00.000                    2
Research                      10       70000 2002-01-02 00:00:00.000                    1
Accounting                    10       40000 2002-01-02 00:00:00.000                    1
Research                       5       65000 2002-01-07 00:00:00.000                    1
Accounting                     5       10000 2002-01-07 00:00:00.000                    1
Marketing                      6       10000 2003-01-01 00:00:00.000                    1
Marketing                      5       40000 2003-01-01 00:00:00.000                    2
Marketing                     10       40000 2003-01-02 00:00:00.000                    1
Accounting                     6       40000 2003-01-02 00:00:00.000                    2
Marketing                      3       30000 2003-01-07 00:00:00.000                    1
(11 rows affected)
1>
2>
3> drop table department;
4> GO
1>
2>

Using an Incrementing Row Number

24>
25> CREATE TABLE employee(
26>    id          INTEGER NOT NULL PRIMARY KEY,
27>    first_name  VARCHAR(10),
28>    last_name   VARCHAR(10),
29>    salary      DECIMAL(10,2),
30>    start_Date  DATETIME,
31>    region      VARCHAR(10),
32>    city        VARCHAR(20),
33>    managerid   INTEGER
34> );
35> GO
1> INSERT INTO employee VALUES (1, "Jason" ,  "Martin", 5890,"2005-03-22","North","Vancouver",3);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (2, "Alison",  "Mathews",4789,"2003-07-21","South","Utown",4);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (3, "James" ,  "Smith",  6678,"2001-12-01","North","Paris",5);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (4, "Celia" ,  "Rice",   5567,"2006-03-03","South","London",6);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (5, "Robert",  "Black",  4467,"2004-07-02","East","Newton",7);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (6, "Linda" ,  "Green" , 6456,"2002-05-19","East","Calgary",8);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (7, "David" ,  "Larry",  5345,"2008-03-18","West","New York",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (8, "James" ,  "Cat",    4234,"2007-07-17","West","Regina",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (9, "Joan"  ,  "Act",    6123,"2001-04-16","North","Toronto",10);
2> GO
(1 rows affected)
1>
2> select * from employee;
3> GO
id          first_name last_name  salary       start_Date              region     city                 managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
          1 Jason      Martin          5890.00 2005-03-22 00:00:00.000 North      Vancouver                      3
          2 Alison     Mathews         4789.00 2003-07-21 00:00:00.000 South      Utown                          4
          3 James      Smith           6678.00 2001-12-01 00:00:00.000 North      Paris                          5
          4 Celia      Rice            5567.00 2006-03-03 00:00:00.000 South      London                         6
          5 Robert     Black           4467.00 2004-07-02 00:00:00.000 East       Newton                         7
          6 Linda      Green           6456.00 2002-05-19 00:00:00.000 East       Calgary                        8
          7 David      Larry           5345.00 2008-03-18 00:00:00.000 West       New York                       9
          8 James      Cat             4234.00 2007-07-17 00:00:00.000 West       Regina                         9
          9 Joan       Act             6123.00 2001-04-16 00:00:00.000 North      Toronto                       10
(9 rows affected)
1>
2>
3>
4> SELECT p.ID,
5> p.First_Name FROM
6> (SELECT ID,First_Name,
7> ROW_NUMBER() OVER (ORDER BY First_Name) RowNumber
8> FROM Employee) p
9> WHERE p.RowNumber BETWEEN 2 AND 4
10> GO
ID          First_Name
----------- ----------
          4 Celia
          7 David
          8 James
(3 rows affected)
1>
2>
3> drop table employee;
4> GO