CODEWITHSUNDEEP
sqlsql-servert-sql
shoogazer
shoogazer

Reputation: 167

Conditionally rank subset of partition

I am trying to rank the below result set.

AccountID FacilityName AccountNumber
1000 Retail ABC-1000
2000 Retail ABC-2000
3000 Non-Retail 123 ABC-3000
500 Non-Retail 456 ABC-500
10000 Retail XYZ-10000
200 Non-Retail 123 XYZ-200
300 Non-Retail 456 XYZ-300

I want to partition the rows by the substring to the left of the dash in AccountNumber (i.e. "ABC" and "XYZ" would each be considered a partition).

I then want to rank the rows of each partition in the below manner.

  1. Any accounts that belong to Retail facility are ranked first (by AccountID ascending)
  2. All other accounts are then subsequently ranked by their AccountID, starting at the max rank obtained in step 1

The desired ranking is below.

AccountID FacilityName AccountNumber Rank
1000 Retail ABC-1000 1
2000 Retail ABC-2000 2
500 Non-Retail 456 ABC-500 3
3000 Non-Retail 123 ABC-3000 4
10000 Retail XYZ-10000 1
200 Non-Retail 123 XYZ-200 2
300 Non-Retail 456 XYZ-300 3

Below I'm currently ranking based on AccountID for all rows of each partition. Not quite sure how to rank the AccountIDs of Retail accounts first.

IF object_id('dbo.AccountsToRank','U') IS NOT NULL DROP TABLE dbo.AccountsToRank
CREATE TABLE dbo.AccountsToRank (AccountID int, FacilityName varchar(max), AccountNumber varchar(max))

INSERT INTO dbo.AccountsToRank (AccountID,FacilityName,AccountNumber)
VALUES (1000, 'Retail', 'ABC-1000'),
        (2000, 'Retail', 'ABC-2000'),
        (3000, 'Non-Retail 123', 'ABC-3000'),
        (500, 'Non-Retail 456', 'ABC-500'),
        (10000, 'Retail', 'XYZ-10000'),
        (200, 'Non-Retail 123', 'XYZ-200'),
        (300, 'Non-Retail 456', 'XYZ-300')

SELECT RANK() OVER(PARTITION BY substring(accountnumber,0,charindex('-',accountnumber)) ORDER BY accountid) AS rnk, *
FROM dbo.AccountsToRank

Upvotes: 0

Views: 129

Answers (3)

Stu
Stu

Reputation: 32599

You can order your rows by using case expressions in the order by clause.

Your sample data is confusing at a glance however, your output is not shown in rank order!

select *, Rank() over(
        partition by left(AccountNumber,CharIndex('-',AccountNumber)-1) 
        order by case when FacilityName ='Retail' then 0 else 1 end, 
         accountid  
        ) as rnk
from AccountsToRank

Upvotes: 2

Serg
Serg

Reputation: 22811

Try

SELECT RANK() OVER(PARTITION BY substring(accountnumber,0,charindex('-',accountnumber)) 
                   ORDER BY case when FacilityName = 'Retail' then 0 else 1 end, accountid) AS rnk, *
FROM dbo.AccountsToRank

Upvotes: 2

typecast
typecast

Reputation: 11

Here is a suggestion: Adding a very high number to the AccountId column based on the value in FacilaityName and ranking accoring to the value in the new column:

Here is the complete query:

select *,RANK() OVER(PARTITION BY substring(accountnumber,0,charindex('-',accountnumber)) ORDER BY ranker) AS rnk from
(select *,AccountID + case when left(FacilityName,3)='Non' then 1000000 else 0 end as ranker 
from dbo.AccountsToRank)a

Upvotes: 0

Related Questions