What is ROW_NUMBER?

Asked

Viewed 29,017 times

22

What is ROW_NUMBER used in the sql server?

How and where we should use it?

Has a simple example of use ?

3 answers

23


ROW_NUMBER is a non-deterministic function that returns a sequential - and potentially volatile - number calculated as (and whenever) a query is executed and serves to list the results of the execution of this query.

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

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

For example, here’s a query normal with field ordering 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 the same query using ROW_NUMBER(). Note that the sorting has been moved into the 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 sort for the data and for generating the values of ROW_NUMBER.

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;

Exit:

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

And an example using the 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 way out would be:

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

The examples were copied from documentation.

See more in the official SQL Server documentation.

11

ROW_NUMBER

The ROW_NUMBER function returns the sequential number of a row 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 (e.g., 1, 2, 3, 4, 5). RANK provides the same numerical value for records of the same position (for example, 1, 2, 2, 4, 5). These values are calculated 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 of the query result set as a single group.

Order_by_clause The ORDER BY clause determines the sequence in which the lines 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

5

What is

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


The syntax

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

Detail

ROW_NUMBER() --Obrigatório

Allocator a number to a record.

OVER() --Obrigatório

That’s where you define yourself as the records shall be numbered.

ORDER BY --Obrigatório

Order some field of light form ASC or descending DESC before applying the numbering.

PARTITION BY --Opcional

Groups the values from some field and makes a separate numbering for each group


Example table

SELECT * FROM PESSOAS

Upshot:

+---+-----------------+-------+------------+
¦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

Upshot:

+----------+-----------------+-------+------------+
¦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 ordered by CITY in order decreasing, next the 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

Upshot:

+----------+-----------------+-------+------------+
¦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 other case the PARTITION BY grouping the results GENERIC, then the ORDER BY ordains the records of each group for CITY, in order decreasing. Finally the records of each group are numbered.

Reference:

Row Number Function in SQL Server - C# Corner

Browser other questions tagged

You are not signed in. Login or sign up in order to post.