Wednesday, September 3, 2008

SQL Server 2005 ranking functions

SELECT SalesOrderID, SubTotal,
ROW_NUMBER() OVER(ORDER BY SubTotal DESC) AS RowNumber,
RANK() OVER(ORDER BY SubTotal DESC) AS Rank,
DENSE_RANK() OVER(ORDER BY SubTotal DESC) AS DenseRank,
NTILE(3) OVER(ORDER BY SubTotal DESC) AS NTile
FROM Sales.SalesOrderHeader
WHERE SalesOrderID IN (51885, 51886, 52031, 56410,
56411, 72585, 65101, 65814, 73976)
ORDER BY SubTotal DESC;


------------------------------------------------------------------------------------------

- ROW_NUMBER { http://msdn2.microsoft.com/en-us/library/ms186734.aspx }
- OVER clause { http://msdn2.microsoft.com/en-us/library/ms189461.aspx }
- ORDER BY clause { http://msdn2.microsoft.com/en-us/library/ms188385.aspx }
- Ranking functions { http://msdn2.microsoft.com/en-us/library/ms189798.aspx }
- What's New in SQL Server 2005 { http://www.microsoft.com/sql/prodinfo/overview/whats-new-in-sqlserver2005.mspx }
- AdventureWorks - SQL Server 2005 Samples and Sample Databases (July 2006) { http://www.microsoft.com/downloads/details.aspx?FamilyId=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en }
- WITH common table expression { http://msdn2.microsoft.com/en-us/library/ms175972.aspx }

No comments:

Follow by Email