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