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.
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.
If you've needed to parse or construct a URI in C#, you've likely done it…
The second installment in my series of C# basics illustrates the proper use of access…
This page details the coding style I've adopted for C# code in my applications. You…
For the new C# section of my website, I wanted to post some notes that…
There are some pretty compelling reasons to lock down your LinkedIn account now. We bet…
We bet you didn't know that your full name, picture, work history, and more may…