What is ROW_NUMBER?

ROW_NUMBER is a non-deterministic function that returns a number (and whenever) the query is executed and serves to enumerate the results of the execution of this query .

Whenever you use the ROW_NUMBER function, you must use the OVER() with a ORDER BY . Not necessarily this ORDER BY needs to be sorting the data, it just determines how the numbers will be generated by the ROW_NUMBER function (and that's why the clause is mandatory).

In the OVER clause it is also possible to use a PARTITION BY clause, it will set when the "count" of ROW_NUMBER should restart.

For example, here is a normal query with sorting by field name .

SELECT 
  name, recovery_model_desc
FROM sys.databases 
WHERE database_id < 5
ORDER BY name ASC;

The result would be something like:

name      recovery_model_desc
----------------------------
master    SIMPLE
model     FULL
msdb      SIMPLE
tempdb    SIMPLE

And here's the same query using ROW_NUMBER() . Note that the sort order was moved into OVER() .

The result would be:

Row#    name     recovery_model_desc
1       master   SIMPLE
2       model    FULL
3       msdb     SIMPLE
4       tempdb   SIMPLE

An example using a different ordering for the data and for generating the ROW_NUMBER values.

SELECT 
  ROW_NUMBER() OVER(ORDER BY name ASC) AS Row#,
  name, recovery_model_desc
FROM sys.databases 
WHERE database_id < 5
Order By Name Desc;

Output:

Row#    name     recovery_model_desc
4       tempdb   SIMPLE
3       msdb     SIMPLE    
2       model    FULL
1       master   SIMPLE

And an example using PARTITION BY recovery_model_desc .

SELECT 
  ROW_NUMBER() OVER(PARTITION BY recovery_model_desc ORDER BY name ASC) 
    AS Row#,
  name, recovery_model_desc
FROM sys.databases WHERE database_id < 5;

The output would look like this:

Row#    name     recovery_model_desc
1       model    FULL
1       master   SIMPLE
2       msdb     SIMPLE
3       tempdb   SIMPLE

The examples have been copied from documentation .

See more in the official SQL Server documentation .

ROW_NUMBER

The ROW_NUMBER function returns the sequential number of a line within a partition of a result set, starting at 1 for the first line on each partition.

ROW_NUMBER and RANK

ROW_NUMBER and RANK are similar. ROW_NUMBER will number all lines sequentially (for example, 1, 2, 3, 4, 5). RANK provides the same numeric value for records of the same position (for example, 1, 2, 2, 4, 5 ). These values are computed in memory and are not physically in the tables.

SYNTAX

ROW_NUMBER ( )   
OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )  

PARTITION BY value_expression Divides the result set produced by the FROM clause into partitions to which the ROW_NUMBER function is applied. Value_expression specifies the column by which the result set is partitioned. If PARTITION BY is not specified, the function treats all rows in the query result set as a single group.

Order_by_clause The ORDER BY clause determines the sequence in which rows are assigned their unique ROW_NUMBER within a specified partition. It is necessary. For more information, see the Over Over Clause

Example 1

SELECT 
  ROW_NUMBER() OVER(PARTITION BY recovery_model_desc ORDER BY name ASC) 
    AS Row#,
  name, recovery_model_desc
FROM sys.databases WHERE database_id < 5;

Example 2

USE AdventureWorks2012;  
GO  
SELECT FirstName, LastName, TerritoryName, ROUND(SalesYTD,2,1),  
ROW_NUMBER() OVER(PARTITION BY TerritoryName ORDER BY SalesYTD DESC) 
  AS Row  
FROM Sales.vSalesPerson  
WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0  
ORDER BY TerritoryName;

Reference: Microsoft Docs documentation ROW_NUMBER function

What is

ROW_NUMBER is a SQL Server function used for numbering of records .

The syntax

ROW_NUMBER() OVER (PARTITION BY CampoA ORDER BY CampoB)

Detail

ROW_NUMBER() --Obrigatório

Responsible for assigning a number to a record .

OVER() --Obrigatório

It is where how is defined as the records will be numbered.

ORDER BY --Obrigatório

Sort some fields in an ascending order ASC or descending DESC before applying the numbering.

PARTITION BY --Opcional

Group the values for some field and separate numbering for each group

Sample table

SELECT * FROM PESSOAS

Result:

+---+-----------------+-------+------------+
¦ID ¦NOME             ¦GENERO ¦CIDADE      |
+---+-----------------+-------+------------+
¦1  ¦DENIS            ¦M      ¦SÃO PAULO   ¦
+---+-----------------+-------+------------+
¦2  ¦JULIANA          ¦F      ¦CAMPINAS    ¦
+---+-----------------+-------+------------+
¦3  ¦LUCAS            ¦M      ¦CAMPINAS    ¦
+---+-----------------+-------+------------+
¦4  ¦MÁRCIA           ¦F      ¦SÃO PAULO   ¦
+---+-----------------+-------+------------+
¦5  ¦RODRIGO          ¦M      ¦CAMPINAS    ¦
+---+-----------------+-------+------------+
¦6  ¦MARIANA          ¦F      ¦CAMPINAS    ¦
+---+-----------------+-------+------------+
¦7  ¦OTÁVIO           ¦M      ¦SÃO PAULO   ¦
+---+-----------------+-------+------------+

This is the table that will be used as an example.

Example 1

ROW_NUMBER() with ORDER BY :

SELECT ROW_NUMBER() OVER (ORDER BY CIDADE DESC) AS NUMERACAO,
NOME, GENERO, CIDADE 
FROM PESSOAS

Result:

+----------+-----------------+-------+------------+
¦NUMERACAO ¦NOME             ¦GENERO ¦CIDADE      |
+----------+-----------------+-------+------------+
¦1         ¦DENIS            ¦M      ¦SÃO PAULO   ¦
+----------+-----------------+-------+------------+
¦2         ¦MÁRCIA           ¦F      ¦SÃO PAULO   ¦
+----------+-----------------+-------+------------+
¦3         ¦OTÁVIO           ¦M      ¦SÃO PAULO   ¦
+----------+-----------------+-------+------------+
¦4         ¦RODRIGO          ¦M      ¦CAMPINAS    ¦
+----------+-----------------+-------+------------+
¦5         ¦MARIANA          ¦F      ¦CAMPINAS    ¦
+----------+-----------------+-------+------------+
¦6         ¦JULIANA          ¦F      ¦CAMPINAS    ¦
+----------+-----------------+-------+------------+
¦7         ¦LUCAS            ¦M      ¦CAMPINAS    ¦
+----------+-----------------+-------+------------+

In this case the results of the table are sorted by CITY in order decreasing , then records are numbered .

Example 2

ROW_NUMBER() with ORDER BY and PARTITION BY :

SELECT ROW_NUMBER() OVER (PARTITION BY GENERO ORDER BY CIDADE DESC) AS NUMERACAO,
NOME, GENERO, CIDADE 
FROM PESSOAS

Result:

+----------+-----------------+-------+------------+
¦NUMERACAO ¦NOME             ¦GENERO ¦CIDADE      |
+----------+-----------------+-------+------------+
¦1         ¦MÁRCIA           ¦F      ¦SÃO PAULO   ¦
+----------+-----------------+-------+------------+
¦2         ¦JULIANA          ¦F      ¦CAMPINAS    ¦
+----------+-----------------+-------+------------+
¦3         ¦MARIANA          ¦F      ¦CAMPINAS    ¦
+----------+-----------------+-------+------------+
¦1         ¦OTÁVIO           ¦M      ¦SÃO PAULO   ¦
+----------+-----------------+-------+------------+
¦2         ¦DENIS            ¦M      ¦SÃO PAULO   ¦
+----------+-----------------+-------+------------+
¦3         ¦LUCAS            ¦M      ¦CAMPINAS    ¦
+----------+-----------------+-------+------------+
¦4         ¦RODRIGO          ¦M      ¦CAMPINAS    ¦
+----------+-----------------+-------+------------+

In this case, contains% results by GENERO , then% orders each group by CITY , in order decreasing . Finally the records of each group are numbered .

Reference:

  

Row Number Function in SQL Server - C # Corner