sqlsql-serverpartitionover-clause

SQL DENSE_RANK and PARTITION BY

I do not fully understand the query results listed for the below:

CREATE TABLE #tmpAccountsTable (ID INT PRIMARY KEY, AccountID INT, 

AccountName varchar(20), CompanyID int, CompanyName varchar(50))
INSERT INTO #tmpAccountsTable (ID, AccountID, AccountName, CompanyID, CompanyName)
SELECT 1 AS ID, 1 AS AccountID, 'ABC Account' AS AccountName, 1 AS CompanyID, 'Stark Enterprise' AS CompanyName UNION
SELECT 2 AS ID, 1 AS AccountID, 'ABC Account' AS AccountName, 1 AS CompanyID, 'Stark Enterprise' AS CompanyName UNION
SELECT 3 AS ID, 2 AS AccountID, 'DEF Account' AS AccountName, 1 AS CompanyID, 'Stark Enterprise' AS CompanyName UNION
SELECT 4 AS ID, 3 AS AccountID, 'GHI Account' AS AccountName, 1 AS CompanyID, 'Stark Enterprise' AS CompanyName UNION
SELECT 5 AS ID, 4 AS AccountID, 'JKL Account' AS AccountName, 1 AS CompanyID, 'Stark Enterprise' AS CompanyName UNION
SELECT 6 AS ID, 1 AS AccountID, 'ABC Account' AS AccountName, 1 AS CompanyID, 'Stark Enterprise' AS CompanyName UNION
SELECT 7 AS ID, 1 AS AccountID, 'ABC Account' AS AccountName, 1 AS CompanyID, 'Stark Enterprise' AS CompanyName UNION
SELECT 8 AS ID, 1 AS AccountID, 'ABC Account' AS AccountName, 1 AS CompanyID, 'Stark Enterprise' AS CompanyName UNION
SELECT 9 AS ID, 2 AS AccountID, 'DEF Account' AS AccountName, 1 AS CompanyID, 'Stark Enterprise' AS CompanyName

SELECT DENSE_RANK() OVER (PARTITION BY AccountID ORDER BY CompanyName) as AccountRANK, * FROM #tmpAccountsTable

DROP TABLE #tmpAccountsTable

Executing the above, I get the following result set:

Result Set

From my understanding of the SELECT DENSE_RANK() OVER (PARTITION BY AccountID ORDER BY CompanyName) as AccountRANK, * FROM #tmpAccountsTable, a partition should have been created for all AccountId's that are the same, and they should have received the same DENSE_RANK() value, but as can be seen, this is not the case.

What am I missing?

Solution

  • If you want a separate value for each AccountId, then that should be part of the ORDER BY, not PARTITION BY:

    SELECT DENSE_RANK() OVER (ORDER BY AccountID, CompanyName) as AccountRANK, *
FROM #tmpAccountsTable;

    The PARTITION BY is used to reset the counter, so each AccountIdstarts over with 1. Then the CompanyNames are ordered. Because they all have the same value, the result is 1.