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 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>