2

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?

monstertjie_za
  • 7,277
  • 8
  • 42
  • 73
  • All of the ranks for AccountID 1 are tied, so they are all 1, same for AccountID 2, 3, and 4. What did you expect it to be? – dfundako Jul 03 '19 at 19:03

1 Answers1

4

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • "The PARTITION BY is used to reset the counter". What exactly do you mean? I was under the impression the PARTITION BY AccountID will create a partition for all AccountID's with the same value, and therefore have the same rank value? – monstertjie_za Jul 03 '19 at 19:06
  • @monstertjie_za . . . I thought that was clear. I mean that each `AccountId` will rank the `CompanyName` values for that account starting at 1. – Gordon Linoff Jul 03 '19 at 19:42