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

Материал из SQL эксперт
Перейти к: навигация, поиск

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

   <source lang="sql">

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


Using an Incrementing Row Number

   <source lang="sql">

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

Источник — «http://sqle.ru/index.php?title=SQL_Server/T-SQL_Tutorial/Analytical_Functions/ROW_NUMBER&oldid=6393»