CODEWITHSUNDEEP
sql-server-2008t-sql
mack
mack

Reputation: 2965

Using SQL Rank() for overall rank and rank within a group

I'm trying to write a query to return the overall rank and a per group rank. I'm able to get the "Overall Rank", but the Territory rank isn't correct.

SELECT        Territory, [Agent Number], [Agent Name], [Commission Level], [Profile End Date], [Prior Year Sales], [Current Year Sales YTD], [Total Sales], Rank() 
                             OVER (PARTITION BY Territory
    ORDER BY [Total Sales]) AS [Territory Rank], Rank() OVER (ORDER BY [Total Sales] DESC) AS [Overall Rank]
    FROM            (SELECT        TerritoryBase.Name AS Territory, ContactExtensionBase.ipl_AgentNumber AS [Agent Number], ContactBase.FullName AS [Agent Name], 
                                                        ContactExtensionBase.ath_activecommissionlevel AS [Commission Level], ContactExtensionBase.ath_profileenddate AS [Profile End Date], 
                                                        CASE WHEN ath_PriorYearSales IS NULL THEN '0' ELSE ath_PriorYearSales END AS [Prior Year Sales], CASE WHEN ath_SalesYTD IS NULL 
                                                        THEN '0' ELSE ath_SalesYTD END AS [Current Year Sales YTD], CASE WHEN ath_PriorYearSales IS NULL 
                                                        THEN '0' ELSE ath_PriorYearSales END + CASE WHEN ath_SalesYTD IS NULL THEN '0' ELSE ath_SalesYTD END AS [Total Sales]
                              FROM            ContactBase INNER JOIN
                                                        ContactExtensionBase ON ContactBase.ContactId = ContactExtensionBase.ContactId INNER JOIN
                                                        TerritoryBase ON ContactExtensionBase.ipl_TerritoryId = TerritoryBase.TerritoryId
                              WHERE        (NOT (ContactExtensionBase.ath_PriorYearSales IS NULL)) AND (ContactExtensionBase.ath_PriorYearSales <> 0) OR
                                                        (NOT (ContactExtensionBase.ath_PriorYearSales IS NULL)) AND (ContactExtensionBase.ath_SalesYTD <> 0) OR
                                                        (ContactExtensionBase.ath_PriorYearSales <> 0) AND (NOT (ContactExtensionBase.ath_SalesYTD IS NULL)) OR
                                                        (NOT (ContactExtensionBase.ath_SalesYTD IS NULL)) AND (ContactExtensionBase.ath_SalesYTD <> 0)) AS DerivedTable1
    ORDER BY [Overall Rank]

Here is a sample result from the above query: Sample Query Results

I know that I do not understand how to use Rank. Can someone help me figure this out?

EDIT: I tried to include what Mark suggested so my rank statements now look like this:

SELECT        Territory, [Agent Number], [Agent Name], [Commission Level], [Profile End Date], [Prior Year Sales], [Current Year Sales YTD], [Total Sales], Rank() 
                         OVER (PARTITION BY [Territory]
ORDER BY [Total Sales] Desc) AS [Territory Rank], Rank() OVER (ORDER BY [Total Sales] Desc) AS [Overall Rank]

Which produces this: enter image description here

This is close, but the territory rank still doesn't seem to work as expected. I'm not sure where number 8 and 9 or the 12 through 14 went inside the territory rank. Am I missing something with how Rank works?

Upvotes: 0

Views: 508

Answers (1)

Mark Sowul
Mark Sowul

Reputation: 10600

Looks like your Territory Rank is ordering by Total Sales without the DESCENDING so it is probably backwards.

Look at Jack and Jerry. They are 'territory ranked' 352 and 353, instead of 1 and 2.

NB: depending on what you're doing, consider how you want to handle ties and note there is also DENSE_RANK, which won't skip numbers if there is a tie.

Upvotes: 1

Related Questions