Friday, November 13, 2009

TSQL : Row count & Total Rows by OVER

Demonstrating OVER, PARTITION BY and ROW_NUMBER to return total number of rows and a incrementing row count.
USE AdventureWorks
GO
SELECT 
COUNT(*) OVER(PARTITION BY NULL) AS TotalRowCount 
,ROW_NUMBER() OVER (ORDER BY [TransactionID] DESC) AS [ASC_ROWNUMBER]
,[TransactionID]
,[ProductID]
,[ReferenceOrderID]
,[ReferenceOrderLineID]
,[TransactionDate]
,[TransactionType]
,[Quantity]
,[ActualCost]
,[ModifiedDate]
FROM [AdventureWorks].[Production].[TransactionHistory]
GO 

No comments: