ROW_NUMBER function returns the sequential number of a row within a result set, starting at 1 for the first row. : ROW_NUMBER « Analytical Functions « SQL Server / T-SQL Tutorial

1. SQL Server / T-SQL Tutorial
2. Analytical Functions
3. ROW_NUMBER

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>