Как получить номер строки в таблице sql

ROW_NUMBER (Transact-SQL)

Нумерует выходные данные результирующего набора. В частности, возвращает последовательный номер строки в секции результирующего набора, 1 соответствует первой строке в каждой из секций.

Функции ROW_NUMBER и RANK похожи. ROW_NUMBER нумерует все строки по порядку (например, 1, 2, 3, 4, 5). RANK назначает одинаковое числовое значение строкам, претендующим на один ранг (например, 1, 2, 2, 4, 5).

ROW_NUMBER — это временное значение, вычисляемое во время выполнения запроса. Сведения о хранении номеров в таблице см. в разделах Свойство IDENTITY и SEQUENCE.

Синтаксис

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

Сведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.

Аргументы

PARTITION BY value_expression
Делит результирующий набор, полученный от предложения FROM, на секции, к которым применяется функция ROW_NUMBER. value_expression определяет столбец, по которому секционируется результирующий набор. Если параметр PARTITION BY не указан, функция обрабатывает все строки результирующего набора запроса как одну группу. Дополнительные сведения см. в статье Предложение OVER (Transact-SQL).

order_by_clause
Предложение ORDER BY определяет последовательность, в которой строкам назначаются уникальные номера с помощью функции ROW_NUMBER в пределах указанной секции. Оно должно указываться обязательно. Дополнительные сведения см. в статье Предложение OVER (Transact-SQL).

Типы возвращаемых данных

bigint

Общие замечания

Нет гарантии того, что строки, возвращенные запросом, использующим ROW_NUMBER() , будут расставлены в одинаковом порядке после каждого выполнения, если не соблюдены указанные ниже условия.

1. Все значения в секционированном столбце являются уникальными.
2. Все значения в столбцах ORDER BY являются уникальными.
3. Сочетания значений из столбца секционирования и столбцов ORDER BY являются уникальными.

Функция ROW_NUMBER() не детерминирована. Дополнительные сведения см. в разделе Deterministic and Nondeterministic Functions.

Примеры

А. Простые примеры

Приведенный ниже запрос возвращает четыре системные таблицы в алфавитном порядке.

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

name	recovery_model_desc
master	ПРОСТОЙ
модель	FULL
msdb	ПРОСТОЙ
tempdb	ПРОСТОЙ

Чтобы добавить столбец с номерами строк перед каждой строкой, добавьте столбец с помощью функции ROW_NUMBER , в данном случае с именем Row# . Предложение ORDER BY необходимо переместить к предложению OVER .

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

Номер строки	name	recovery_model_desc
1	master	ПРОСТОЙ
2	модель	FULL
3	msdb	ПРОСТОЙ
4	tempdb	ПРОСТОЙ

Добавление предложения PARTITION BY для столбца recovery_model_desc приведет к тому, что нумерация начнется заново при изменении значения 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; 

Номер строки	name	recovery_model_desc
1	модель	FULL
1	master	ПРОСТОЙ
2	msdb	ПРОСТОЙ
3	tempdb	ПРОСТОЙ

B. Возврат номера строки для salespeople

В следующем примере вычисляется номер строки для продавцов в Adventure Works Cycles на основе их годового рейтинга продаж.

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

 Row FirstName LastName SalesYTD --- ----------- ---------------------- ----------------- 1 Linda Mitchell 4251368.54 2 Jae Pak 4116871.22 3 Michael Blythe 3763178.17 4 Jillian Carson 3189418.36 5 Ranjit Varkey Chudukatil 3121616.32 6 José Saraiva 2604540.71 7 Shu Ito 2458535.61 8 Tsvi Reiter 2315185.61 9 Rachel Valdez 1827066.71 10 Tete Mensa-Annan 1576562.19 11 David Campbell 1573012.93 12 Garrett Vargas 1453719.46 13 Lynn Tsoflias 1421810.92 14 Pamela Ansman-Wolfe 1352577.13 

C. Возврат подмножества строк

В следующем примере показан расчет номеров всех строк в таблице SalesOrderHeader в порядке OrderDate с последующим возвращением строк с номерами от 50 до 60 включительно.

USE AdventureWorks2022; GO WITH OrderedOrders AS ( SELECT SalesOrderID, OrderDate, ROW_NUMBER() OVER (ORDER BY OrderDate) AS RowNumber FROM Sales.SalesOrderHeader ) SELECT SalesOrderID, OrderDate, RowNumber FROM OrderedOrders WHERE RowNumber BETWEEN 50 AND 60; 

D. Использование ROW_NUMBER() с PARTITION

В следующем примере аргумент PARTITION BY используется для секционирования результирующего набора запроса по столбцу TerritoryName . Предложение ORDER BY , указанное в предложении OVER , упорядочивает строки каждой секции по столбцу SalesYTD . Предложение ORDER BY в инструкции SELECT упорядочивает полный результирующий набор запроса по TerritoryName .

USE AdventureWorks2022; GO SELECT FirstName, LastName, TerritoryName, ROUND(SalesYTD,2,1) AS SalesYTD, 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; 

 FirstName LastName TerritoryName SalesYTD Row --------- -------------------- ------------------ ------------ --- Lynn Tsoflias Australia 1421810.92 1 José Saraiva Canada 2604540.71 1 Garrett Vargas Canada 1453719.46 2 Jillian Carson Central 3189418.36 1 Ranjit Varkey Chudukatil France 3121616.32 1 Rachel Valdez Germany 1827066.71 1 Michael Blythe Northeast 3763178.17 1 Tete Mensa-Annan Northwest 1576562.19 1 David Campbell Northwest 1573012.93 2 Pamela Ansman-Wolfe Northwest 1352577.13 3 Tsvi Reiter Southeast 2315185.61 1 Linda Mitchell Southwest 4251368.54 1 Shu Ito Southwest 2458535.61 2 Jae Pak United Kingdom 4116871.22 1 

Примеры: Azure Synapse Analytics и система платформы аналитики (PDW)

Д. Возврат номера строки для salespeople

В приведенном ниже примере возвращается ROW_NUMBER для торговых представителей в зависимости от установленной для них квоты продаж.

-- Uses AdventureWorks SELECT ROW_NUMBER() OVER(ORDER BY SUM(SalesAmountQuota) DESC) AS RowNumber, FirstName, LastName, CONVERT(varchar(13), SUM(SalesAmountQuota),1) AS SalesQuota FROM dbo.DimEmployee AS e INNER JOIN dbo.FactSalesQuota AS sq ON e.EmployeeKey = sq.EmployeeKey WHERE e.SalesPersonFlag = 1 GROUP BY LastName, FirstName; 

Здесь приводится частичный результирующий набор.

 RowNumber FirstName LastName SalesQuota --------- --------- ------------------ ------------- 1 Jillian Carson 12,198,000.00 2 Linda Mitchell 11,786,000.00 3 Michael Blythe 11,162,000.00 4 Jae Pak 10,514,000.00 

F. Использование ROW_NUMBER() с PARTITION

Следующий пример демонстрирует использование функции ROW_NUMBER с аргументом PARTITION BY . В результате функция ROW_NUMBER нумерует строки в каждой секции.

-- Uses AdventureWorks SELECT ROW_NUMBER() OVER(PARTITION BY SalesTerritoryKey ORDER BY SUM(SalesAmountQuota) DESC) AS RowNumber, LastName, SalesTerritoryKey AS Territory, CONVERT(varchar(13), SUM(SalesAmountQuota),1) AS SalesQuota FROM dbo.DimEmployee AS e INNER JOIN dbo.FactSalesQuota AS sq ON e.EmployeeKey = sq.EmployeeKey WHERE e.SalesPersonFlag = 1 GROUP BY LastName, FirstName, SalesTerritoryKey; 

Здесь приводится частичный результирующий набор.

 RowNumber LastName Territory SalesQuota --------- ------------------ --------- ------------- 1 Campbell 1 4,025,000.00 2 Ansman-Wolfe 1 3,551,000.00 3 Mensa-Annan 1 2,275,000.00 1 Blythe 2 11,162,000.00 1 Carson 3 12,198,000.00 1 Mitchell 4 11,786,000.00 2 Ito 4 7,804,000.00 

MySQL: порядковый номер строки

Порядковый номер строки, что это совершенно не тоже самое, что номер AUTO_INCREMENT, бывает необходимо получить для многих задач.

Если у вас значение AUTO_INCREMENT идет без дырок - можно использовать его, а если из таблицы удалялись записи или AUTO_INCREMENT вовсе отсутствует?

Как узнать порядковый номер строки в MySQL запросе

К огромному сожалению в MySQL нет стандартной функции, которая бы отдавала нам порядковый номер каждой строки. В других базах данных она есть. В ORACLE для этого есть переменная rownum , а в PostgreSQL и msSQL функция ROW_NUMBER() .

Пишем свой вариант для MySQL:

set @n:=0; select @n:=@n+1 as `num`, `name` from `ВАША_ТАБЛИЦА`;

Результатом выполнения запроса будет:

1 АртКиев 2 Дизайн 3 Студио

Функция ROW_NUMBER стр. 2

В MySQL ранжирующих/оконных функций не было до версии 8.0, однако была возможность использовать переменные непосредственно в запросе SQL. В частности, с помощью переменных можно решить задачу нумерации строк запроса. Продемонстрируем это на примере, который рассматривался на предыдущей странице.

В третьей строке запроса выполняется инициализация переменной и присваивается ей начальное значение. В итоге каждая строка таблицы Trip будет соединяться со строкой из одного столбца, содержащего 0 (просто декартово произведение).

В первой строке запроса значение переменной инкрементируется на 1, что происходит при вычислении каждой следующей строки в порядке, заданном предложением ORDER BY. В итоге мы получаем нумерацию строк в порядке сортировки.

Если вы опустите инициализацию переменной, то можете получить правильный результат. Но это не гарантировано, в чем можно убедиться, повторно выполнив этот же запрос в текущей сессии соединения с базой данных. Вы должны получить продолжение нумерации с максимального значения переменной @i, достигнутого на предыдущем запуске скрипта.

Мы также можем перенумеровать строки для каждой компании отдельно, т.е. сымитировав поведение PARTITION BY в запросе

Консоль

Выполнить

Идея решения состоит в следующем. Введем еще одну переменную для хранения номера компании. При инициализации присвоим ей несуществующий номер (например, 0). Затем для каждой строки будем проверять, совпадает ли номер с номером компании текущей строки. Если значения совпадают, будем инкрементировать, если нет, сбрасывать в 1. Наконец, будем присваивать переменной номер компании из текущей строки. Дело в том, что проверка выполняется до присвоения, тем самым мы сравниваем текущее значение номера компании с номером компании из предыдущей строки (в заданном порядке сортировки). Теперь сам запрос.

Или, коль скоро вы отошли от стандарта, можно использовать функцию IF , чтобы сократить запись:

Проверить эти запросы вы можете из консоли, выбрав из списка MySQL.

Нумерация строк в соответствии с порядком, заданном значениями первичного ключа стр. 1

Естественно, нумероваться строки должны в соответствии с некоторым порядком. Пусть этот порядок задается столбцом первичного ключа, то есть в порядке возрастания (или убывания) значений в этом единственном столбце. Для определенности предположим, что нам нужно перенумеровать модели в таблице Product, где номер модели как раз является первичным ключом. Существенным здесь является то, значения первичного ключа не содержат дубликатов и NULL -значений, в результате чего имеется принципиальная возможность установить однозначное соответствие между номером модели и номером строки в заданном порядке сортировки моделей.

Рассмотрим сначала следующий запрос:

Консоль

Выполнить

Из сказанного выше ясно, что это количество раз, которое каждая из моделей встречается во втором столбце результирующего набора как раз и будет порядковым номером модели при сортировке моделей по возрастанию.

Таким образом, чтобы решить нашу задачу нумерации достаточно пересчитать модели в правом столбце, что нетрудно сделать при помощи группировки и использования агрегатной функции COUNT :

Консоль

Выполнить

Не будем экономить место и представим результат выполнения этого запроса:

Для нумерации в обратном порядке достаточно поменять знак неравенства на противоположный.

Если ваша СУБД поддерживает ранжирующие функции, то пронумеровать строки можно совсем просто:

Консоль

Выполнить