Tested on: SQL Server 2016 Developer Edition
Accurate as of: January, 2018
In this query, I’ve added a record number column to the result set using OVER and ORDER BY clauses with the ROW_NUMBER function in T-SQL.
Note that the ORDER BY clause is required. The OVER() clause tells the SQL Engine to sort data on the specified column (in this case OrderId) and assign numbers as per the sort results.
SELECT ( ROW_NUMBER() OVER (ORDER BY OrderId) ) AS RowNumber,
GrandTotal, CustomerId, PurchaseDate
FROM Orders
--- Rick's Code Snippet Archive
--- Snippet #D08DA59C43B3ECDBD2DF39E2BDDEF56B (TSQL)
--- For the latest version of this code or to post a question or comment about it, visit:
--- http://www.SevenDaysOfSchema.com/tsql-examples/show-row-numbers-in-result-set/
Questions or comments about this script? Be a part of the conversation. It only takes a minute to post a comment.
In this Script
Numbers the output of a result set. More specifically, returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
`ROW_NUMBER` and `RANK` are similar. `ROW_NUMBER` numbers all rows sequentially (for example 1, 2, 3, 4, 5). `RANK` provides the same numeric value for ties (for example 1, 2, 2, 4, 5).
NOTE: `ROW_NUMBER` is a temporary value calculated when the query is run. To persist numbers in a table, see IDENTITY Property and SEQUENCE.
ROW_NUMBER ( )
OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )
/*
A. Simple examples
*/
SELECT
name, recovery_model_desc
FROM sys.databases
WHERE database_id < 5
ORDER BY name ASC;
/*
B. Returning the row number for salespeople
*/
USE AdventureWorks2012;
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;
/*
C. Returning a subset of rows
*/
USE AdventureWorks2012;
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. Using ROW_NUMBER() with PARTITION
*/
USE AdventureWorks2012;
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;
/*
E. Returning the row number for salespeople
*/
-- 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;
/*
F. Using ROW_NUMBER() with PARTITION
*/
-- 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;
Related Topics: DENSE_RANK NTILE RANK
Ranking Functions: DENSE_RANK NTILE RANK
Retrieves one or more rows or columns from the database. The UNION, EXCEPT, and INTERSECT operators can be used between queries to combine or compare their results into one result set. <Rick’s Tip> The SELECT syntax used for Azure SQL Data Warehouse and Parallel Data Warehouse differs from that used for SQL Server and Azure SQL Database.
Permissions: Selecting data requires SELECT permission on the table or view, which could be inherited from a higher scope such as SELECT permission on the schema or CONTROL permission on the table. Or requires membership in the db_datareader or db_owner fixed database roles, or the sysadmin fixed server role. Creating a new table using SELECTINTO also requires both the CREATETABLE permission, and the ALTERSCHEMA permission on the schema that owns the new table.
-- Syntax for SQL Server and Azure SQL Database
&lgt;SELECT statement> ::=
[ WITH { [ XMLNAMESPACES ,] [ &lgt;common_table_expression> [,...n] ] } ]
&lgt;query_expression>
[ ORDER BY { order_by_expression | column_position [ ASC | DESC ] }
[ ,...n ] ]
[ &lgt;FOR Clause>]
[ OPTION ( &lgt;query_hint> [ ,...n ] ) ]
&lgt;query_expression> ::=
{ &lgt;query_specification> | ( &lgt;query_expression> ) }
[ { UNION [ ALL ] | EXCEPT | INTERSECT }
&lgt;query_specification> | ( &lgt;query_expression> ) [...n ] ]
&lgt;query_specification> ::=
SELECT [ ALL | DISTINCT ]
[TOP ( expression ) [PERCENT] [ WITH TIES ] ]
&lgt; select_list >
[ INTO new_table ]
[ FROM { &lgt;table_source> } [ ,...n ] ]
[ WHERE &lgt;search_condition> ]
[ &lgt;GROUP BY> ]
[ HAVING &lgt; search_condition > ]
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse
[ WITH &lgt;common_table_expression> [ ,...n ] ]
SELECT &lgt;select_criteria>
[;]
&lgt;select_criteria> ::=
[ TOP ( top_expression ) ]
[ ALL | DISTINCT ]
{ * | column_name | expression } [ ,...n ]
[ FROM { table_source } [ ,...n ] ]
[ WHERE &lgt;search_condition> ]
[ GROUP BY &lgt;group_by_clause> ]
[ HAVING &lgt;search_condition> ]
[ ORDER BY &lgt;order_by_expression> ]
[ OPTION ( &lgt;query_option> [ ,...n ] ) ]
/* A. Using SELECT to retrieve rows and columns */ SELECT * FROM DimEmployee ORDER BY LastName; /* B. Using SELECT with column headings and calculations */ SELECT FirstName, LastName, BaseRate, BaseRate * 40 AS GrossPay FROM DimEmployee ORDER BY LastName; /* C. Using DISTINCT with SELECT */ SELECT DISTINCT Title FROM DimEmployee ORDER BY Title; /* D. Using GROUP BY */ SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales FROM FactInternetSales GROUP BY OrderDateKey ORDER BY OrderDateKey; /* E. Using GROUP BY with multiple groups */ SELECT OrderDateKey, PromotionKey, AVG(SalesAmount) AS AvgSales, SUM(SalesAmount) AS TotalSales FROM FactInternetSales GROUP BY OrderDateKey, PromotionKey ORDER BY OrderDateKey; /* F. Using GROUP BY and WHERE */ SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales FROM FactInternetSales WHERE OrderDateKey > '20020801' GROUP BY OrderDateKey ORDER BY OrderDateKey; /* G. Using GROUP BY with an expression */ SELECT SUM(SalesAmount) AS TotalSales FROM FactInternetSales GROUP BY (OrderDateKey * 10); /* H. Using GROUP BY with ORDER BY */ SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales FROM FactInternetSales GROUP BY OrderDateKey ORDER BY OrderDateKey; /* I. Using the HAVING clause */ SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales FROM FactInternetSales GROUP BY OrderDateKey HAVING OrderDateKey > 20010000 ORDER BY OrderDateKey;
Related Topics: Hints SELECT Examples
T-SQL Query Elements: CONTAINS EXPLAIN FREETEXT FROM GROUP BY HAVING IS NULL PIVOT and UNPIVOT PREDICT READTEXT TOP UPDATE UPDATETEXT WHERE WRITETEXT
Specifies the tables, views, derived tables, and joined tables used in DELETE, SELECT, and UPDATE statements in SQL Server 2017. In the SELECT statement, the FROM clause is required except when the select list contains only constants, variables, and arithmetic expressions (no column names).
Permissions: Requires the permissions for the DELETE, SELECT, or UPDATE statement.
-- Syntax for SQL Server and Azure SQL Database
[ FROM { &lgt;table_source> } [ ,...n ] ]
&lgt;table_source> ::=
{
table_or_view_name [ [ AS ] table_alias ]
[ &lgt;tablesample_clause> ]
[ WITH ( &lgt; table_hint > [ [ , ]...n ] ) ]
| rowset_function [ [ AS ] table_alias ]
[ ( bulk_column_alias [ ,...n ] ) ]
| user_defined_function [ [ AS ] table_alias ]
| OPENXML &lgt;openxml_clause>
| derived_table [ [ AS ] table_alias ] [ ( column_alias [ ,...n ] ) ]
| &lgt;joined_table>
| &lgt;pivoted_table>
| &lgt;unpivoted_table>
| @variable [ [ AS ] table_alias ]
| @variable.function_call ( expression [ ,...n ] )
[ [ AS ] table_alias ] [ (column_alias [ ,...n ] ) ]
| FOR SYSTEM_TIME &lgt;system_time>
}
&lgt;tablesample_clause> ::=
TABLESAMPLE [SYSTEM] ( sample_number [ PERCENT | ROWS ] )
[ REPEATABLE ( repeat_seed ) ]
&lgt;joined_table> ::=
{
&lgt;table_source> &lgt;join_type> &lgt;table_source> ON &lgt;search_condition>
| &lgt;table_source> CROSS JOIN &lgt;table_source>
| left_table_source { CROSS | OUTER } APPLY right_table_source
| [ ( ] &lgt;joined_table> [ ) ]
}
&lgt;join_type> ::=
[ { INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } } [ &lgt;join_hint> ] ]
JOIN
&lgt;pivoted_table> ::=
table_source PIVOT &lgt;pivot_clause> [ [ AS ] table_alias ]
&lgt;pivot_clause> ::=
( aggregate_function ( value_column [ [ , ]...n ])
FOR pivot_column
IN ( &lgt;column_list> )
)
&lgt;unpivoted_table> ::=
table_source UNPIVOT &lgt;unpivot_clause> [ [ AS ] table_alias ]
&lgt;unpivot_clause> ::=
( value_column FOR pivot_column IN ( &lgt;column_list> ) )
&lgt;column_list> ::=
column_name [ ,...n ]
&lgt;system_time> ::=
{
AS OF &lgt;date_time>
| FROM &lgt;start_date_time> TO &lgt;end_date_time>
| BETWEEN &lgt;start_date_time> AND &lgt;end_date_time>
| CONTAINED IN (&lgt;start_date_time> , &lgt;end_date_time>)
| ALL
}
&lgt;date_time>::=
&lgt;date_time_literal> | @date_time_variable
&lgt;start_date_time>::=
&lgt;date_time_literal> | @date_time_variable
&lgt;end_date_time>::=
&lgt;date_time_literal> | @date_time_variable
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse
FROM { &lgt;table_source> [ ,...n ] }
&lgt;table_source> ::=
{
[ database_name . [ schema_name ] . | schema_name . ] table_or_view_name [ AS ] table_or_view_alias
| derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]
| &lgt;joined_table>
}
&lgt;joined_table> ::=
{
&lgt;table_source> &lgt;join_type> &lgt;table_source> ON search_condition
| &lgt;table_source> CROSS JOIN &lgt;table_source>
| left_table_source { CROSS | OUTER } APPLY right_table_source
| [ ( ] &lgt;joined_table> [ ) ]
}
&lgt;join_type> ::=
[ INNER ] [ &lgt;join hint> ] JOIN
| LEFT [ OUTER ] JOIN
| RIGHT [ OUTER ] JOIN
| FULL [ OUTER ] JOIN
&lgt;join_hint> ::=
REDUCE
| REPLICATE
| REDISTRIBUTE
/*
Arguments
*/
SELECT p.ProductID, v.BusinessEntityID
FROM Production.Product AS p
JOIN Purchasing.ProductVendor AS v
ON (p.ProductID = v.ProductID);
/*
A. Using a simple FROM clause
*/
SELECT TerritoryID, Name
FROM Sales.SalesTerritory
ORDER BY TerritoryID ;
/*
B. Using the TABLOCK and HOLDLOCK optimizer hints
*/
BEGIN TRAN
SELECT COUNT(*)
FROM HumanResources.Employee WITH (TABLOCK, HOLDLOCK) ;
/*
C. Using the SQL-92 CROSS JOIN syntax
*/
wql
SELECT e.BusinessEntityID, d.Name AS Department
FROM HumanResources.Employee AS e
CROSS JOIN HumanResources.Department AS d
ORDER BY e.BusinessEntityID, d.Name ;
/*
D. Using the SQL-92 FULL OUTER JOIN syntax
*/
-- The OUTER keyword following the FULL keyword is optional.
SELECT p.Name, sod.SalesOrderID
FROM Production.Product AS p
FULL OUTER JOIN Sales.SalesOrderDetail AS sod
ON p.ProductID = sod.ProductID
ORDER BY p.Name ;
/*
E. Using the SQL-92 LEFT OUTER JOIN syntax
*/
SELECT p.Name, sod.SalesOrderID
FROM Production.Product AS p
LEFT OUTER JOIN Sales.SalesOrderDetail AS sod
ON p.ProductID = sod.ProductID
ORDER BY p.Name ;
/*
F. Using the SQL-92 INNER JOIN syntax
*/
-- By default, SQL Server performs an INNER JOIN if only the JOIN
-- keyword is specified.
SELECT p.Name, sod.SalesOrderID
FROM Production.Product AS p
INNER JOIN Sales.SalesOrderDetail AS sod
ON p.ProductID = sod.ProductID
ORDER BY p.Name ;
/*
G. Using the SQL-92 RIGHT OUTER JOIN syntax
*/
SELECT st.Name AS Territory, sp.BusinessEntityID
FROM Sales.SalesTerritory AS st
RIGHT OUTER JOIN Sales.SalesPerson AS sp
ON st.TerritoryID = sp.TerritoryID ;
/*
H. Using HASH and MERGE join hints
*/
SELECT p.Name AS ProductName, v.Name AS VendorName
FROM Production.Product AS p
INNER MERGE JOIN Purchasing.ProductVendor AS pv
ON p.ProductID = pv.ProductID
INNER HASH JOIN Purchasing.Vendor AS v
ON pv.BusinessEntityID = v.BusinessEntityID
ORDER BY p.Name, v.Name ;
/*
I. Using a derived table
*/
SELECT RTRIM(p.FirstName) + ' ' + LTRIM(p.LastName) AS Name, d.City
FROM Person.Person AS p
INNER JOIN HumanResources.Employee e ON p.BusinessEntityID = e.BusinessEntityID
INNER JOIN
(SELECT bea.BusinessEntityID, a.City
FROM Person.Address AS a
INNER JOIN Person.BusinessEntityAddress AS bea
ON a.AddressID = bea.AddressID) AS d
ON p.BusinessEntityID = d.BusinessEntityID
ORDER BY p.LastName, p.FirstName;
/*
J. Using TABLESAMPLE to read data from a sample of rows in a table
*/
SELECT *
FROM Sales.Customer TABLESAMPLE SYSTEM (10 PERCENT) ;
/*
K. Using APPLY
*/
SELECT DeptID, DeptName, DeptMgrID, EmpID, EmpLastName, EmpSalary
FROM Departments d CROSS APPLY dbo.GetReports(d.DeptMgrID) ;
/*
L. Using CROSS APPLY
*/
USE master;
GO
SELECT dbid, object_id, query_plan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle);
GO
/*
M. Using FOR SYSTEM_TIME
*/
SELECT DepartmentNumber,
DepartmentName,
ManagerID,
ParentDepartmentNumber
FROM DEPARTMENT
FOR SYSTEM_TIME AS OF '2014-01-01'
WHERE ManagerID = 5;
/*
N. Using the INNER JOIN syntax
*/
-- Uses AdventureWorks
SELECT fis.SalesOrderNumber, dp.ProductKey, dp.EnglishProductName
FROM FactInternetSales AS fis
INNER JOIN DimProduct AS dp
ON dp.ProductKey = fis.ProductKey;
/*
O. Using the LEFT OUTER JOIN and RIGHT OUTER JOIN syntax
*/
-- Uses AdventureWorks
SELECT fis.SalesOrderNumber, dp.ProductKey, dp.EnglishProductName
FROM FactInternetSales AS fis
LEFT OUTER JOIN DimProduct AS dp
ON dp.ProductKey = fis.ProductKey;
/*
P. Using the FULL OUTER JOIN syntax
*/
-- Uses AdventureWorks
SELECT dst.SalesTerritoryKey, dst.SalesTerritoryRegion, fis.SalesOrderNumber
FROM DimSalesTerritory AS dst
FULL OUTER JOIN FactInternetSales AS fis
ON dst.SalesTerritoryKey = fis.SalesTerritoryKey
ORDER BY fis.SalesOrderNumber;
/*
Q. Using the CROSS JOIN syntax
*/
-- Uses AdventureWorks
SELECT dst.SalesTerritoryKey, fis.SalesOrderNumber
FROM DimSalesTerritory AS dst
CROSS JOIN FactInternetSales AS fis
ORDER BY fis.SalesOrderNumber;
/*
R. Using a derived table
*/
-- Uses AdventureWorks
SELECT CustomerKey, LastName
FROM
(SELECT * FROM DimCustomer
WHERE BirthDate > '01/01/1970') AS DimCustomerDerivedTable
WHERE LastName = 'Smith'
ORDER BY LastName;
/*
S. REDUCE join hint example
*/
-- Uses AdventureWorks
EXPLAIN SELECT SalesOrderNumber
FROM
(SELECT fis.SalesOrderNumber, dp.ProductKey, dp.EnglishProductName
FROM DimProduct AS dp
INNER REDUCE JOIN FactInternetSales AS fis
ON dp.ProductKey = fis.ProductKey
) AS dTable
ORDER BY SalesOrderNumber;
/*
T. REPLICATE join hint example
*/
-- Uses AdventureWorks
EXPLAIN SELECT SalesOrderNumber
FROM
(SELECT fis.SalesOrderNumber, dp.ProductKey, dp.EnglishProductName
FROM DimProduct AS dp
INNER REPLICATE JOIN FactInternetSales AS fis
ON dp.ProductKey = fis.ProductKey
) AS dTable
ORDER BY SalesOrderNumber;
/*
U. Using the REDISTRIBUTE hint to guarantee a Shuffle move for a distribution incompatible join
*/
-- Uses AdventureWorks
EXPLAIN
SELECT dp.ProductKey, fis.SalesOrderNumber, fis.TotalProductCost
FROM DimProduct AS dp
INNER REDISTRIBUTE JOIN FactInternetSales AS fis
ON dp.ProductKey = fis.ProductKey;
Related Topics: CONTAINSTABLE DELETE FREETEXTTABLE INSERT OPENQUERY OPENROWSET Operators UPDATE WHERE
T-SQL Query Elements: CONTAINS EXPLAIN FREETEXT GROUP BY HAVING IS NULL PIVOT and UNPIVOT PREDICT READTEXT TOP UPDATE UPDATETEXT WHERE WRITETEXT
About the Archive This script is part of my personal code snippet library, which I’ve been posting to this site using the WordPress automation processes I’m working on.
