ROW_NUMBER (Transact-SQL)
Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
Transact-SQL Syntax Conventions
- <partition_by_clause>
-
Divides the result set produced by the FROM clause into partitions to which the ROW_NUMBER function is applied. For the syntax of PARTITION BY, see OVER Clause (Transact-SQL).
- <order_by_clause>
-
Determines the order in which the ROW_NUMBER value is assigned to the rows in a partition. For more information, see ORDER BY Clause (Transact-SQL). An integer cannot represent a column when the <order_by_clause> is used in a ranking function.
 Note: |
|---|
The ORDER BY in the OVER clause orders ROW_NUMBER. If you add an ORDER BY clause to the SELECT statement that orders by a column(s) other than 'Row Number' the result set will be ordered by the outer ORDER BY.
|
The following example returns the ROW_NUMBER for the salespeople in AdventureWorks based on the year-to-date sales.
USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
,ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number'
,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s
INNER JOIN Person.Contact c
ON s.SalesPersonID = c.ContactID
INNER JOIN Person.Address a
ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL
AND SalesYTD <> 0;
The following example returns rows with numbers 50 to 60 inclusive in the order of the OrderDate.
USE AdventureWorks;
GO
WITH OrderedOrders AS
(
SELECT SalesOrderID, OrderDate,
ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
FROM Sales.SalesOrderHeader
)
SELECT *
FROM OrderedOrders
WHERE RowNumber BETWEEN 50 AND 60;
The following example shows using the PARTITION BY argument.
USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
,ROW_NUMBER() OVER
(PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS 'Row Number'
,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s
INNER JOIN Person.Contact c
ON s.SalesPersonID = c.ContactID
INNER JOIN Person.Address a
ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL
AND SalesYTD <> 0;
Change History
| Release | History |
|---|---|
|
17 July 2006 |
|
- 5/13/2011
- RedFilterxx
I have two tables and i want to join them.
Table 1 example:
STUDENT_ID STUDENT_NAME STUDENT ADDRESS
1234 SEM ABC ROAD
1235 SID OBC ROAD
1236 NAD CFA ROAD
Table 2 example
STUDENT_ID PHONE TYPE STUDENT_PHONE
1234 work 234567890
1234 hom 288263766
1235 work 386386483
1236 cell 872878977
I want join both tables in a way that i will transpose phone type into rows with respect to STUDENT ID.
Please help
Sem
- 10/15/2010
- SEM HAS
