Row_number (transact-sql)row_number (transact-sql)

MySQL ROW_NUMBER() function examples

Let’s use the table from the sample database for the demonstration:


1) Assigning sequential numbers to rows

The following statement uses the function to assign a sequential number to each row from the table:

Here is the output:

2) Finding top N rows of every group

You can use the function for the queries that find the top N rows for every group, for example, top three sales employees of every sales channel, top five high-performance products of every category.

The following statement finds the top three products that have the highest inventory of every product line:

In this example,

  • First, we used the function to rank the inventory of all products in each product line by partitioning all products by product line and ordering them by quantity in stock in descending order.  As the result, each product is assigned a rank based on its quantity in stock. and the rank is reset for each product line.
  • Then, we selected only products whose rank is less than or equal to three.

The following shows the output:

3) Removing duplicate rows

You can use the to turn non-unique rows into unique rows and then delete the duplicate rows. Consider the following example.

First, create a table with some duplicate values:

Second, use the function to divide the rows into partitions by all columns. The row number will restart for each unique set of rows.

As you can see from the output, the unique rows are the ones whose the row number equals one.

Third, you can use the common table expression (CTE) to return the duplicate rows and delete statement to remove:

Notice that the MySQL does not support CTE based delete, therefore, we had to join the original table with the CTE as a workaround.

4) Pagination using  function

Because the assigns each row in the result set a unique number, you can use it for pagination.

Suppose, you need to display a list of products with 10 products per page. To get the products for the second page, you use the following query:

Here is the output:

In this tutorial, you have learned how to use the MySQL function to generate a sequential number for each row in a result set.

  • Was this tutorial helpful?

Using ROWNUM with Subqueries

A more appropriate way to use the ROWNUM pseudocolumn is with a subquery.

The basic steps are:

  1. Write your query
  2. Order your query
  3. Enclose this query within a subquery
  4. Filter the outer query using ROWNUM

Let’s see an example. Say we wanted to see the students who are in the top 5 when ordered by last_name.

One way to do this might be:

Result:

ROWNUM FIRST_NAME LAST_NAME ADDRESS_STATE
3 Tom Capper Nevada
4 Mark Holloway New York
2 Susan Johnson Colorado
1 John Smith New York
5 Steven Webber New York

However, this is not correct, because the ROWNUM is used to limit the number of rows, and then the ORDER BY is done. This shows the top 5 in the table before the ORDER BY.

To do this correctly, enclose it in a subquery as mentioned earlier.

Result:

ROWNUM FIRST_NAME LAST_NAME ADDRESS_STATE
1 Mark Anderson California
2 Julie Armstrong Texas
3 Tom Capper Nevada
4 Andrew Cooper Texas
5 Tanya Hall Texas

This now shows the student records, ordered by last_name, and then only the top 5, which is the result we were looking for.

So, that’s what Oracle ROWNUM and Oracle ROW_NUMBER does, and how they are different. Be careful when you use the ORDER BY as well, as it may not give you the results you need.


Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

ПримерыExamples

A.A. Простые примерыSimple examples

Приведенный ниже запрос возвращает четыре системные таблицы в алфавитном порядке.The following query returns the four system tables in alphabetic order.

Результирующий набор:Here is the result set.

namename recovery_model_descrecovery_model_desc
mastermaster ПРОСТОЙSIMPLE
modelmodel FULLFULL
msdbmsdb ПРОСТОЙSIMPLE
tempdbtempdb ПРОСТОЙSIMPLE

Чтобы добавить столбец с номерами строк перед каждой строкой, добавьте столбец с помощью функции , в данном случае с именем .To add a row number column in front of each row, add a column with the function, in this case named . Предложение необходимо переместить к предложению .You must move the clause up to the clause.

Результирующий набор:Here is the result set.

Номер строкиRow# namename recovery_model_descrecovery_model_desc
11 mastermaster ПРОСТОЙSIMPLE
22 modelmodel FULLFULL
33 msdbmsdb ПРОСТОЙSIMPLE
44 tempdbtempdb ПРОСТОЙSIMPLE

Добавление предложения для столбца приведет к тому, что нумерация начнется заново при изменении значения .Adding a clause on the column, will restart the numbering when the value changes.

Результирующий набор:Here is the result set.

Номер строкиRow# namename recovery_model_descrecovery_model_desc
11 modelmodel FULLFULL
11 mastermaster ПРОСТОЙSIMPLE
22 msdbmsdb ПРОСТОЙSIMPLE
33 tempdbtempdb ПРОСТОЙSIMPLE

Б.B. Возврат номера строки для salespeopleReturning the row number for salespeople

В следующем примере показан расчет номера строки для salespeople в Компания Adventure Works CyclesAdventure Works Cycles, выполняемый на основе ранжирования продаж за текущий год.The following example calculates a row number for the salespeople in Компания Adventure Works CyclesAdventure Works Cycles based on their year-to-date sales ranking.

Результирующий набор:Here is the result set.

В.C. Возврат подмножества строкReturning a subset of rows

В следующем примере показан расчет номеров всех строк в таблице в порядке с последующим возвращением строк с номерами от до включительно.The following example calculates row numbers for all rows in the table in the order of the and returns only rows to inclusive.

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

В следующем примере аргумент используется для секционирования результирующего набора запроса по столбцу .The following example uses the argument to partition the query result set by the column . Предложение , указанное в предложении , упорядочивает строки каждой секции по столбцу .The clause specified in the clause orders the rows in each partition by the column . Предложение в инструкции упорядочивает полный результирующий набор запроса по .The clause in the statement orders the entire query result set by .

Результирующий набор:Here is the result set.

Пример

Рассмотрим пример Oracle, чтобы понять, как применять %ROWTYPE в Oracle PL/SQL. Например:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

DECLARE — %ROWTYPE может включать все столбцы в таблице …

emp_recemployees%ROWTYPE; — … или подмножество столбцов на основе курсора.

CURSORc1IS

SELECTdepartment_id,department_nameFROMdepartments;

dept_recc1%ROWTYPE; — Можно даже создать %ROWTYPE со столбцами из нескольких таблиц.

CURSORc2IS

SELECTemployee_id,email,employees.manager_id,location_id

FROMemployees,departments

WHEREemployees.department_id=departments.department_id;

join_recc2%ROWTYPE; BEGIN — Мы знаем, что EMP_REC может содержать строку из таблицы EMPLOYEES.

SELECT*INTOemp_recFROMemployeesWHEREROWNUM<2; — Мы можем ссылаться на поля EMP_REC, используя имена столбцов — из таблицы EMPLOYEES.

IFemp_rec.department_id=20ANDemp_rec.last_name=’JOHNSON’THEN

emp_rec.salary:=emp_rec.salary*1.15;

ENDIF;

END;

В этом примере Oracle PL/SQL мы объявили переменную emp_rec основанную на записи таблицы employees. Также мы объявили переменную join_rec основанную на записи курсора c2 созданного из полей таблиц employees и departments.

PostgreSQL ROW_NUMBER() function examples

We will use the table created in the PostgreSQL window function tutorial to demonstrate the functionality of the function.

The following is the data in the table:

See the following query.

Because we did not use the clause, the function considers the whole result set as a partition.

The clause sorts the result set by , therefore, the  function assigns integer values to the rows based on the   order.

In the following query, we change the column in the clause to product_name, the function assigns the integer values to each row based on the product name order.


In the following query, we use the clause to divide the window into subsets based on the values in the   column. In this case, the function assigns one to the starting row of each partition and increases by one for the next row within the same partition.

The clause sorts the rows in each partition by the values in the column.

Функции RANK и DENSE_RANK

Функции RANK и DENSE RANK похожи на ROW_NUMBER, но в отличие от нее они не создают уникальные значения в оконной секции. При упорядочении окна по возрастанию «Обычный ранг» RANK вычисляется как единица плюс число строк со значением, по которому выполняется упорядочение, меньшим, чем текущее значение в секции. «Плотный ранг» DENSE_RANK вычисляется как единица плюс число уникальных строк со значением, по которому выполняется упорядочение, меньшим, чем текущее значение в секции. При упорядочении окна по убыванию RANK вычисляется как единица плюс число строк со значением, по которому выполняется упорядочение, большим, чем текущее значение в секции. DENSE_RANK вычисляется как единица плюс число уникальных строк со значением, по которому выполняется упорядочение, большим, чем текущее значение в секции.

Вот пример запроса, вычисляющего номера строк, ранги и «уплотненные» ранги. При этом используется секционирование окна по умолчанию и упорядочение по orderdate DESC:

Атрибут orderdate не уникален. Но заметьте, что при этом номера строк уникальны. Значения RANK и DENSE_RANK не уникальны. Все строки с одной датой заказа, например 2008-05-05, получили одинаковый «неплотный» ранг 5 и «плотный» ранг 2. Ранг 5 означает, что есть четыре строки с большими (более поздними) датами заказов (упорядочение ведется по убыванию), а «плотный» ранг 2 означает, что есть одна более поздняя уникальная дата.

Альтернативное решение, заменяющее RANK и DENSE_RANK и не использующие оконные функции, создается просто:

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

Детерминизм

Как вы уже сами, наверное, поняли, что как RANK, так и DENSE_RANK детерминистичны по определению. При одном значении упорядочения — независимо от его уникальности — возвращается одно и то же значение ранга. Вообще говоря, эти две функции обычно интересны, если упорядочение неуникально. Если упорядочение уникально, они дают те же результаты, что и Функция ROW_NUMBER.

What Is Oracle ROWNUM?

Oracle ROWNUM is a pseudocolumn that assigns a number to each row returned by a query. It’s assigned before an ORDER BY is performed, so you shouldn’t order by the ROWNUM value.

You might think that ROWNUM is a function in Oracle.

However, it’s not a function. It’s a “pseudocolumn”. It acts like a column but it’s not defined on the table.

ROWNUM assigns a number to each row returned by a query. It is assigned before an ORDER BY is performed.

For now, let’s see an example of the Oracle ROWNUM pseudocolumn.

Result:

ROWNUM FIRST_NAME LAST_NAME ADDRESS_STATE
1 John Smith New York
2 Susan Johnson Colorado
3 Tom Capper Nevada
4 Mark Holloway New York
5 Steven Webber New York
6 Julie Armstrong Texas
7 Michelle Randall Florida
8 Andrew Cooper Texas
9 Robert Pickering Colorado
10 Tanya Hall Texas
11 Jarrad Winston Utah
12 Mary Taylor Oregon
13 Mark Anderson California
14 John Rogers Nevada

As you can see, the ROWNUM value has been added as the first column of the result set.

What Is Oracle ROW_NUMBER?

Oracle ROW_NUMBER is an Oracle analytic function. It assigns a number to each row, depending on the parameters to the function, which define the ordering within a group of rows.

Many analytic functions follow a similar syntax, and ROW_NUMBER is one of those.

Let’s see an example of the Oracle ROW_NUMBER function.

Result:

FIRST_NAME LAST_NAME ADDRESS_STATE ROW_NUMBER_VALUE
Mark Anderson California 1
Susan Johnson Colorado 1
Robert Pickering Colorado 2
Michelle Randall Florida 1
Tom Capper Nevada 1
John Rogers Nevada 2
Mark Holloway New York 1
John Smith New York 2
Steven Webber New York 3
Mary Taylor Oregon 1
Julie Armstrong Texas 1
Andrew Cooper Texas 2
Tanya Hall Texas 3
Jarrad Winston Utah 1

As you can see, it shows all of the records in the student table. It has ordered the records by address_state, and then by last_name.

For each record that has the same address_state value, a different number has been assigned. This is based on the records being ordered by the last_name value, then assigned a sequential number.

Susan Johnson has a value of 1, and Robert Pickering has a value of 2, because they are both in Colorado but Johnson comes before Pickering. Mark Anderson also has a value of 1, but the address_state is different, so this is OK.

参数Arguments

PARTITION BY value_expression PARTITION BY value_expression将 FROM 子句生成的结果集划分为应用 ROW_NUMBER 函数的分区。Divides the result set produced by the FROM clause into partitions to which the ROW_NUMBER function is applied. value_expression 指定对结果集进行分区所依据的列 。value_expression specifies the column by which the result set is partitioned. 如果未指定 ,则此函数将查询结果集的所有行视为单个组。If is not specified, the function treats all rows of the query result set as a single group. 有关详细信息,请参阅 OVER 子句 (Transact-SQL)。For more information, see OVER Clause (Transact-SQL).

order_by_clause order_by_clause 子句可确定在特定分区中为行分配唯一 的顺序。The clause determines the sequence in which the rows are assigned their unique within a specified partition. 它是必需的。It is required. 有关详细信息,请参阅 OVER 子句 (Transact-SQL)。For more information, see OVER Clause (Transact-SQL).

Функция NTILE

Эта функция позволяет разбивать строки в секции окна на примерно равные по размеру подгруппы (tiles) в соответствии с заданным числом подгрупп и упорядочением окна. Допустим, что нужно разбить строки представления OrderValues на 10 подгрупп одинакового размера на основе упорядочения по val. В представлении 830 строк, поэтому требуется 10 подгрупп, размер каждой будет составлять 83 (830 деленное на 10). Поэтому первым 83 строкам (одной десятой части), упорядоченным по val, будет назначен номер группы 1, следующим 83 строкам — номер подгруппы 2 и т. д. Вот запрос, вычисляющий номера как строк, так и подгрупп:


Если вы думаете, что разбиение на подгруппы похоже на разбиение на страницы, хочу вас предупредить, что не стоит их путать. При разбиении на страницы, размер страницы является константой, а число страниц меняется динамически — оно определяется делением числа строк в результате запроса на размер страницы. При разбиении на подгруппы число подгрупп является константой, а размер подгруппы меняется и определяется как число строк деленное на заданное число подгрупп. Ясно, для чего нужно разбиение на страницы, а разбиение на подгруппы обычно используется для аналитических задач — когда нужно распределить данные среди заданного числа равных по размеру сегментов с использованием упорядочения по определенному измерению.

Но вернемся к результату запроса, вычисляющего номера как строк, так и подгрупп: как видите они тесно связаны друг с другом. По сути, можно считать, что номер подгруппы вычисляется на основе номера строки. В предыдущем разделе мы говорили, что если упорядочение окна не является уникальным, функция ROW_NUMBER является недетерминистической. Если разбиение на подгруппы принципиально основано на номерах строк, то это означает, что вычисление NTILE также недетерминистично, если упорядочение окна не уникально. Это означает, что у данного запроса может быть несколько правильных результатов. Можно посмотреть на это с другой стороны: двум строкам с одним значением упорядочения могут быть назначены разные номера подгрупп. Если нужен гарантированный детерминизм, можно следовать моим рекомендациям по получению детерминистических номеров строк, а именно добавить в упорядочение окна дополнительный параметр:

Теперь у запроса только один правильный результат. Ранее, при описании функции NTILE я пояснил, что она позволяет разбить строки в секции окна на примерно равные подгруппы. Я использовал слово «примерно», потому что число строк, полученное в базовом запросе, может не делиться нацело на число подгрупп. Допустим, вы хотите разбить строки представления OrderValues на 100 подгрупп. При делении 830 на 100 получаем частное 8 и остаток 30. Это означает, что базовая размерность подгрупп будет 8, но часть подгрупп получать дополнительную строку. Функция NTILE не пытается распределять дополнительные строки среди подгрупп с равным расстоянием между подгруппами — она просто добавляет по одному ряду в первые подгруппы, пока не распределит остаток. При наличии остатка 30 размерность первых 30 подгрупп будет на единицу больше базовой размерности. Поэтому первые 30 будут содержать 9 рядов, а последние 70 — 8, как показано в следующем запросе:

Следуя привычному методу, попытаемся создать альтернативные решения, заменяющие функцию NTILE и не содержащие оконных функций.

Я покажу один способ решения задачи. Для начала, вот код, который вычисляет число подгрупп по заданным размерности, числу подгрупп и числу строк:

Вычисление вполне очевидно. Для входных данных код возвращает 5 в качестве числа подгрупп.

Затем применим эту процедуру к строкам представления OrderValues. Используйте агрегат COUNT, чтобы получить размерность результирующего набора, а не входные данные @cnt, а также примените описанную ранее логику для вычисления номеров строк без использования оконных функций вместо входных данных @rownum:

Как обычно, не пытайтесь повторить это в производственной среде! Это пример предназначен для обучения, а его производительность в SQL Server ужасна по сравнению с функцией NTILE.

SQL Справочник

SQL Ключевые слова ADD ADD CONSTRAINT ALTER ALTER COLUMN ALTER TABLE ALL AND ANY AS ASC BACKUP DATABASE BETWEEN CASE CHECK COLUMN CONSTRAINT CREATE CREATE DATABASE CREATE INDEX CREATE OR REPLACE VIEW CREATE TABLE CREATE PROCEDURE CREATE UNIQUE INDEX CREATE VIEW DATABASE DEFAULT DELETE DESC DISTINCT DROP DROP COLUMN DROP CONSTRAINT DROP DATABASE DROP DEFAULT DROP INDEX DROP TABLE DROP VIEW EXEC EXISTS FOREIGN KEY FROM FULL OUTER JOIN GROUP BY HAVING IN INDEX INNER JOIN INSERT INTO INSERT INTO SELECT IS NULL IS NOT NULL JOIN LEFT JOIN LIKE LIMIT NOT NOT NULL OR ORDER BY OUTER JOIN PRIMARY KEY PROCEDURE RIGHT JOIN ROWNUM SELECT SELECT DISTINCT SELECT INTO SELECT TOP SET TABLE TOP TRUNCATE TABLE UNION UNION ALL UNIQUE UPDATE VALUES VIEW WHERE

MySQL Функции Функции строк ASCII CHAR_LENGTH CHARACTER_LENGTH CONCAT CONCAT_WS FIELD FIND_IN_SET FORMAT INSERT INSTR LCASE LEFT LENGTH LOCATE LOWER LPAD LTRIM MID POSITION REPEAT REPLACE REVERSE RIGHT RPAD RTRIM SPACE STRCMP SUBSTR SUBSTRING SUBSTRING_INDEX TRIM UCASE UPPER Функции чисел ABS ACOS ASIN ATAN ATAN2 AVG CEIL CEILING COS COT COUNT DEGREES DIV EXP FLOOR GREATEST LEAST LN LOG LOG10 LOG2 MAX MIN MOD PI POW POWER RADIANS RAND ROUND SIGN SIN SQRT SUM TAN TRUNCATE Функции дат ADDDATE ADDTIME CURDATE CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURTIME DATE DATEDIFF DATE_ADD DATE_FORMAT DATE_SUB DAY DAYNAME DAYOFMONTH DAYOFWEEK DAYOFYEAR EXTRACT FROM_DAYS HOUR LAST_DAY LOCALTIME LOCALTIMESTAMP MAKEDATE MAKETIME MICROSECOND MINUTE MONTH MONTHNAME NOW PERIOD_ADD PERIOD_DIFF QUARTER SECOND SEC_TO_TIME STR_TO_DATE SUBDATE SUBTIME SYSDATE TIME TIME_FORMAT TIME_TO_SEC TIMEDIFF TIMESTAMP TO_DAYS WEEK WEEKDAY WEEKOFYEAR YEAR YEARWEEK Функции расширений BIN BINARY CASE CAST COALESCE CONNECTION_ID CONV CONVERT CURRENT_USER DATABASE IF IFNULL ISNULL LAST_INSERT_ID NULLIF SESSION_USER SYSTEM_USER USER VERSION

SQL Server функции Функции строк ASCII CHAR CHARINDEX CONCAT Concat with + CONCAT_WS DATALENGTH DIFFERENCE FORMAT LEFT LEN LOWER LTRIM NCHAR PATINDEX QUOTENAME REPLACE REPLICATE REVERSE RIGHT RTRIM SOUNDEX SPACE STR STUFF SUBSTRING TRANSLATE TRIM UNICODE UPPER Функции чисел ABS ACOS ASIN ATAN ATN2 AVG CEILING COUNT COS COT DEGREES EXP FLOOR LOG LOG10 MAX MIN PI POWER RADIANS RAND ROUND SIGN SIN SQRT SQUARE SUM TAN Функции дат CURRENT_TIMESTAMP DATEADD DATEDIFF DATEFROMPARTS DATENAME DATEPART DAY GETDATE GETUTCDATE ISDATE MONTH SYSDATETIME YEAR Функции расширений CAST COALESCE CONVERT CURRENT_USER IIF ISNULL ISNUMERIC NULLIF SESSION_USER SESSIONPROPERTY SYSTEM_USER USER_NAME

MS Access функции Функции строк Asc Chr Concat with & CurDir Format InStr InstrRev LCase Left Len LTrim Mid Replace Right RTrim Space Split Str StrComp StrConv StrReverse Trim UCase Функции чисел Abs Atn Avg Cos Count Exp Fix Format Int Max Min Randomize Rnd Round Sgn Sqr Sum Val Функции дат Date DateAdd DateDiff DatePart DateSerial DateValue Day Format Hour Minute Month MonthName Now Second Time TimeSerial TimeValue Weekday WeekdayName Year Другие функции CurrentUser Environ IsDate IsNull IsNumeric

SQL ОператорыSQL Типы данныхSQL Краткий справочник

SQL Учебник

SQL ГлавнаяSQL ВведениеSQL СинтаксисSQL SELECTSQL SELECT DISTINCTSQL WHERESQL AND, OR, NOTSQL ORDER BYSQL INSERT INTOSQL Значение NullSQL Инструкция UPDATESQL Инструкция DELETESQL SELECT TOPSQL MIN() и MAX()SQL COUNT(), AVG() и …SQL Оператор LIKESQL ПодстановочныйSQL Оператор INSQL Оператор BETWEENSQL ПсевдонимыSQL JOINSQL JOIN ВнутриSQL JOIN СлеваSQL JOIN СправаSQL JOIN ПолноеSQL JOIN СамSQL Оператор UNIONSQL GROUP BYSQL HAVINGSQL Оператор ExistsSQL Операторы Any, AllSQL SELECT INTOSQL INSERT INTO SELECTSQL Инструкция CASESQL Функции NULLSQL ХранимаяSQL Комментарии

SQL Учебник

SQL ГлавнаяSQL ВведениеSQL СинтаксисSQL SELECTSQL SELECT DISTINCTSQL WHERESQL AND, OR, NOTSQL ORDER BYSQL INSERT INTOSQL Значение NullSQL Инструкция UPDATESQL Инструкция DELETESQL SELECT TOPSQL MIN() и MAX()SQL COUNT(), AVG() и …SQL Оператор LIKESQL ПодстановочныйSQL Оператор INSQL Оператор BETWEENSQL ПсевдонимыSQL JOINSQL JOIN ВнутриSQL JOIN СлеваSQL JOIN СправаSQL JOIN ПолноеSQL JOIN СамSQL Оператор UNIONSQL GROUP BYSQL HAVINGSQL Оператор ExistsSQL Операторы Any, AllSQL SELECT INTOSQL INSERT INTO SELECTSQL Инструкция CASESQL Функции NULLSQL ХранимаяSQL Комментарии

Demo

In this section, we’ll take a look at the SQL ROW_NUMBER function. For the entire demo, I’ve used AdventureWorks2016 database.

How to use ROW_NUMBER in SQL Query

The following examples, we’ll see the use of OVER clause.

Let us get the list of all the customers by projecting the columns such as SalesOrderID, OrderDate, SalesOrderNumber, SubTotal, TotalDue and RowNum. The Row_Number function is applied with the order of the CustomerID column. The temporary value starts from 1 assigned based on the order of the CustomerID, and the values are continued till the last rows of the table. The order of CustomerID is not guaranteed because we don’t specify the ORDER BY clause in the query.

1 2 3 4 5 6 7 8 9 10 11

USEAdventureWorks2016;

GO

SELECTROW_NUMBER()OVER(

ORDERBYCustomerID)ASRowNum,

CustomerID,

SalesOrderID,

OrderDate,

SalesOrderNumber,

SubTotal,

TotalDue

FROMSales.SalesOrderHeader;

How to use Order by clause

The following example uses the ORDER BY clause in the query. The ORDER BY clause in the query applied on the SalesOrderID column. We can see that the rows in output are still ordered and returned. The Row_Number is still applied to the CustomerID. The output indicates that the ORDER BY of the query and the ORDER BY of the OVER Clause are independent of the output.

1 2 3 4 5 6 7 8 9 10 11 12

USEAdventureWorks2016;

GO

SELECTROW_NUMBER()OVER(

ORDERBYCustomerID)ASRowNum,

CustomerID,

SalesOrderID,

OrderDate,

SalesOrderNumber,

SubTotal,

TotalDue

FROMSales.SalesOrderHeader

ORDERBYSalesOrderID;

How to use multiple columns with the OVER clause

The following example you can see that we have listed customerID and OrderDate in the ORDER BY clause. This gives the customer details with the most recent order details along with the sequence of numbers assigned to the entire result-set.

1 2 3 4 5 6 7 8 9 10

USEAdventureWorks2016;

GO

SELECTROW_NUMBER()OVER(ORDERBYCustomerID,OrderDateDESC)ASRowNum,

CustomerID,

SalesOrderID,

OrderDate,

SalesOrderNumber,

SubTotal,

TotalDue

FROMSales.SalesOrderHeader

How to use the SQL ROW_NUMBER function with PARTITION

The following example uses PARTITION BY clause on CustomerID and OrderDate fields. In the output, you can see that the customer 11019 has three orders for the month 2014-Jun. In this case, the partition is done on more than one column.

The partition is a combination of OrderDate and CustomerID. The Row_Number will start over for each unique combination of OrderDate and CustomerID. In this way, it’s easy to find the customer who has placed more than one order on the same day.

1 2 3 4 5 6 7 8 9 10 11 12

USEAdventureWorks2016;

GO

SELECTROW_NUMBER()OVER(PARTITIONBYCustomerID,

DATEADD(MONTH,DATEDIFF(MONTH,,OrderDate),)

ORDERBYSubTotalDESC)ASMonthlyOrders,

CustomerID,

SalesOrderID,

OrderDate,

SalesOrderNumber,

SubTotal,

TotalDue

FROMSales.SalesOrderHeader;

How to return a subset of rows using CTE and ROW_NUMBER

The following example we are going to analyze SalesOrderHeader to display the top five largest orders placed by each customer every month. Using the Month function, the orderDate columns is manipulated to fetch the month part. In this way, the sales corresponding to specific month (OrderDate) along with customer (CustomerID) is partitioned.

To list the five largest orders in each month for each customer, a CTE is used. A window is created on the partition data and it is assigned with the values and then the CTE is being called to fetch the largest orders.

1 2 3 4 5 6 7 8 9 10 11 12

WITHcte

AS(SELECTROW_NUMBEROVER(PARTITIONBYcustomerID,MONTH(OrderDate)ORDERBYSubTotalDESC,TotalDueDESC)ASROW_NUM,

CustomerID,

MONTH(OrderDate)Month,

SubTotal,

TotalDue,

OrderDate

FROMSales.SalesOrderHeader

)

SELECT*

FROMcte

WHEREROW_NUM<=5


С этим читают