T-SQL for database series (System built-in functions)

Row_number ()

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 groupings, and the value computed by this function represents the sequential number of each set of internally ordered (the only contiguous )

Instance:

Initializing data

Create TableEmployee (Empidint, DeptIDint, salarydecimal(Ten,2))Insert  intoEmployeeValues(1,Ten,5500.00)Insert  intoEmployeeValues(2,Ten,4500.00)Insert  intoEmployeeValues(3, -,1900.00)Insert  intoEmployeeValues(4, -,4800.00)Insert  intoEmployeeValues(5, +,6500.00)Insert  intoEmployeeValues(6, +,14500.00)Insert  intoEmployeeValues(7, +,44500.00)Insert  intoEmployeeValues(8, -,6500.00)Insert  intoEmployeeValues(9, -,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 *  Over  by ORDER  by desc  from Employee

T-SQL for database series (System built-in functions)