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.
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:
Browser other questions tagged sql sql-server terminology
You are not signed in. Login or sign up in order to post.
Select first record within a segmentation in SQL Server I made an issue detailing the answer here.
– Marconi
@Marconi, do not explain in detail the function in his reply.
– Marco Souza
Got it, I’ll see if I can come up with a very clear answer here if someone doesn’t post it first.
– Marconi