ProgrammingMSAccess.com

The ProgrammingMSAccess.COM Site

One Transact-sql select STATEMENT to contrast the rank and dense_rank functions. and a second one to rank within data partitions.  Both samples are for use with the SQL Server express or another edition of sql server 2005.  the sample data are from the adventureworks database.

USE AdventureWorks
GO

--Rows ranked and ordered by SalesQuota
--from both the RANK and DENSE_RANK functions
SELECT FirstName + ' ' + LastName 'Name', SalesYTD, SalesQuota,
TerritoryGroup,
RANK() OVER(ORDER BY SalesQuota DESC) AS 'Rank',
Dense_RANK() OVER(ORDER BY SalesQuota DESC ) AS 'Dense Rank'
FROM Sales.vSalesPerson
GO


--Rows partitioned by TerritoryGroup as well as dense ranked
--and ordered by SalesYTD within SalesQuota
SELECT FirstName + ' ' + LastName 'Name', SalesYTD, SalesQuota,
TerritoryGroup,
Dense_RANK() OVER(PARTITION BY TerritoryGroup
ORDER BY SalesQuota DESC, SalesYTD DESC) AS 'Dense Rank'
FROM Sales.vSalesPerson
GO

Want to understand  SQL Server so that you can program it to do more tasks like this?  Get Programming Microsoft SQL Server 2000 with Microsoft Visual Basic .NET by Rick Dobson from Microsoft Press.  Learn more about the book by clicking here.  The webmaster's DVD on programming T-SQL also represents an excellent opportunity for learning T-SQL coding.

Copyright 2005 CAB, Inc. All rights reserved. Republication or redistribution of CAB, Inc. content, including by framing or similar means, is expressly prohibited without the prior written consent of CAB, Inc. CAB, Inc. shall not be liable for any errors in the content, or for any actions taken in reliance thereon.