grammar :ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)

To put it simply row_number() from 1 Start , Return a number for each grouped record , there ROW_NUMBER() OVER (ORDER BY xlh DESC) First of all. xlh Column descending , No more items after descending xlh The record returns a sequence number .
Example :
xlh           row_num
1700              1
1500              2
1085              3
710                4

row_number() OVER (PARTITION BY COL1 ORDER BY COL2) Express basis COL1 grouping , Within the group according to COL2 Sort , The value calculated by this function represents the sequence number of each group after internal sorting ( The only continuous )

example :

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

The data shows

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

demand : Group by Department , Display the salary level of each department

Expected results :

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

SQL ROW_NUMBER() OVER More articles on the basic usage of functions

  1. sql ROW_NUMBER() Sorting function ( turn )

    1 Use row_number() Function to number : Such as select email,customerID, ROW_NUMBER() over(order by psd) as rows from QT_Cus ...

  2. sql ROW_NUMBER() Sorting function

    1. Use row_number() Function to number : Such as 1 select email,customerID, ROW_NUMBER() over(order by psd) as rows from QT_ ...

  3. SQL With As usage Sql Four ranking functions (ROW_NUMBER、RANK、DENSE_RANK、NTILE) brief introduction

    Sql Four ranking functions (ROW_NUMBER.RANK.DENSE_RANK.NTILE) brief introduction   The ranking function is Sql Server2005 New features , Here is a brief introduction to their respective usages and differences . Let's create a new one O ...

  4. ROW_NUMBER() OVER The basic usage of functions

    ROW_NUMBER() OVER The basic usage of functions from :http://www.cnblogs.com/icebutterfly/archive/2009/08/05/1539657.html grammar ...

  5. ROW_NUMBER() OVER The basic usage of function , It can also be used to remove duplicate lines

    grammar :ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN) To put it simply row_number() from 1 Start , Return a number for each grouped record , there ROW ...

  6. Sql Server REPLACE Use of functions ;SQL in patindex Function usage

    Sql Server REPLACE Use of functions REPLACE Replace all the second given string expressions that appear in the first string expression with the third expression . grammar REPLACE ( ''string_replace1'' ...

  7. SQL in CONVERT() Function usage detailed explanation

    SQL in CONVERT Function format : CONVERT(data_type,expression[,style]) Parameter description : expression It's anything that works Microsoft SQL Server ...

  8. ROW_NUMBER() OVER() The usage function ;( grouping , Sort ),partition by

    Reprint :https://www.cnblogs.com/alsf/p/6344197.html 1.row_number() over() Sorting function : (1) row_number() over() Group arrangement ...

  9. ROW_NUMBER() OVER() The usage function ;( grouping , Sort ),partition by ( turn )

    1.row_number() over() Sorting function : (1) row_number() over() Group sorting function : In the use of row_number() over() Function time ,over() The group and row inside ...

Random recommendation

  1. SQL CURSOR

    SET NOCOUNT ON; DECLARE @vendor_id int, @vendor_name nvarchar(50),     @message varchar(80), @produc ...

  2. mybatis course

    http://www.yihaomen.com/article/java/302.htm

  3. turn : use Delphi Write the installation program

    http://www.okbase.net/doc/details/931   I haven't personally verified , Only collect When you finish developing an application , Then you also need to make a standardized installation program for the software , This is the last step in programming ...

  4. windows The way to open God Mode in

    Create a new folder anywhere Name the folder "GodMode.{ED7BA470-8E54-465E-825C-99712043E01C}" enter ,ok 了

  5. console One of my little mistakes

    I used to use the console console.log("a"); Every time log Is a variable or a direct string , But today I am log There's an expression in it , When we write the expressions next , I found his strange output ...

  6. WebDataTree Use XML Do data source binding data

    English version of the original link :http://www.infragistics.com/help/topic/e5f07b51-ee2d-4a33-aaac-2f43cffff327 The version of the control used is :Infrag ...

  7. LR11 Installation precautions

    One . install Microsoft Visual c++2005 sp1 Runtime components , The command line option syntax error will be prompted , type “ command /?” Get help information resolvent : Get into LoadRunner11 download \loadrunne ...

  8. JAVAEE——BOS Logistics projects 13:Quartz summary 、 Create a scheduled task 、 Use JavaMail Send E-mail 、HighCharts summary 、 Realize the regional distribution map

    1 Study plan 1.Quartz summary n Quartz Introduction and download n  Introductory cases n Quartz Execute the process n cron expression 2. stay BOS Project use Quartz Create a scheduled task 3. stay BOS Project use Jav ...

  9. shader Advanced texture learning summary

    Recently saw shader Advanced texture of Make a conclusion. review ! shader It's going to be won sooner or later

  10. PHP call Python Problems encountered in the interface process

    stay php call python Script time , First turn on the python Related components are well installed . use Python The command detects the running script without reporting an error . In the process of calling the interface 1. It's not open python Script permissions terms of settlement : hold python The script is in ...