The basic usage of the SQL row_number () over function

Syntax: Row_number () Over (PARTITION by column ORDER by column)

In a nutshell, Row_number () returns a number for each grouped record starting at 1, where Row_number () over (order by XLH DESC) returns an ordinal of the XLH column in descending order and no XLH record after descending.
Example:
XLH row_num
1700 1
1500 2
1085 3
710 4

Row_number () over (PARTITION by COL1 ORDER by COL2) indicates that sorting is based on COL1 within the grouping according to COL2 grouping, and the value computed by this function represents the sequential number of each set of internally ordered (contiguous unique within the group)

Instance:

Initializing data

CREATE TABLE employee (empid int, deptid int, salary decimal (10,2))
INSERT into employee values (1,10,5500.00)
INSERT into employee values (2,10,4500.00)
INSERT into employee values (3,20,1900.00)
INSERT into employee values (4,20,4800.00)
INSERT into employee values (5,40,6500.00)
INSERT into employee values (6,40,14500.00)
INSERT into employee values (7,40,44500.00)
INSERT into employee values (8,50,6500.00)
INSERT into employee values (9,50,7500.00)

Data is displayed as

Empid DeptID Salary
----------- ----------- ---------------------------------------
1 10 5500.00
2 10 4500.00
3 20 1900.00
4 20 4800.00
5 40 6500.00
6 40 14500.00
7 40 44500.00
8 50 6500.00
9 50 7500.00

Requirements: Displays the salary level of each department according to the Department group

Expected Result:

Empid DeptID Salary Rank
----------- ----------- --------------------------------------- --------------------
1 10 5500.00 1
2 10 4500.00 2
4 20 4800.00 1
3 20 1900.00 2
7 40 44500.00 1
6 40 14500.00 2
5 40 6500.00 3
9 50 7500.00 1
8 50 6500.00 2

SQL script:

SELECT *, Row_number () over (partition by DeptID ORDER by salary desc) rank from employee

Reprinted from Http://www.cnblogs.com/gy51Testing/archive/2012/07/26/2609832.html

The basic usage of the SQL row_number () over function