sql query to get code with max rank

[User 9232486]

Any help to figure out this query is highly appreciated. I have three tables. (Scripts to load test data copied below). First table #UserAccounts has UserAccounts, AccountType and BillingAccountKey. Second table #BillingTransactions had Billingtransactionkey(surrogate key), BillingAccountKey and BillingCode. UserAccount and BillingAccountKey has 1 to 1 relationship. Third Table #BillingCodeRank has BillingCode and Rank. I want to see the billing code associated with each UserAccount (with account type 'O') that has the max rank. Example:UserAccount 456 has BillingAccountKey =2 and has three billing codes (222,333,444) having rank 6,5,4 recpectively. The result should show BillingCode 222 associated with UserAccount 456 because codee 222 has the max rank among the three codes. The final result from the test data would be as shown below: Result: UserAccount AccountType BillingAccountKey BillingCode Rank 456 O 2 222 6 789 O 3 111 7 102 O 5 333 5

--Scripts to load test data

SELECT * INTO #UserAccount FROM (
SELECT 123 AS UserAccounts, 'I' AS AccountType, 1 AS BillingAccountKey
UNION ALL
SELECT 456, 'O', 2
UNION ALL
SELECT 789, 'O', 3
UNION ALL
SELECT 101, 'I', 4
UNION ALL
SELECT 102, 'O', 5) A

SELECT * FROM #UserAccount

--================================================================
SELECT * INTO #BillingTransactions FROM (
SELECT 1 AS BillingTransactionKey, 1 AS BillingAccountKey, 111 AS BillingCode
UNION ALL
SELECT 2, 2, 222
UNION ALL
SELECT 3, 2, 333
UNION ALL
SELECT 4, 2, 444
UNION ALL
SELECT 5, 3, 111
UNION ALL
SELECT 6, 3, 555
UNION ALL
SELECT 7, 3, 666
UNION ALL
SELECT 8, 3, 222
UNION ALL
SELECT 9, 5, 333
UNION ALL
SELECT 10, 5, 777)A

SELECT * FROM #BillingTransactions

--===============================================
SELECT * INTO #BillingCodeRank FROM(
SELECT 111 AS BillingCode, 7 AS [Rank]
UNION ALL
SELECT 222, 6
UNION ALL
SELECT 333, 5
UNION ALL
SELECT 444, 4
UNION ALL
SELECT 555, 3
UNION ALL
SELECT 666, 2
UNION ALL
SELECT 777, 1
UNION ALL
SELECT 888, 1
UNION ALL
SELECT 999, 3
UNION ALL
SELECT 101, 5)A
SELECT * FROM #BillingCodeRank

Thank you for you help.

[Richard Deeming]

Something like this should work:

WITH cteOrderedBillingCodes As
(
SELECT
T.BillingAccountKey,
T.BillingCode,
R.Rank,
ROW_NUMBER() OVER (PARTITION BY T.BillingAccountKey ORDER BY R.Rank DESC) As RN
FROM
#BillingTransactions As T
INNER JOIN #BillingCodeRank As R
ON R.BillingCode = T.BillingCode
)
SELECT
A.UserAccount,
A.AccountType,
A.BillingAccountKey,
C.BillingCode,
C.Rank
FROM
#UserAccount As A
LEFT JOIN cteOrderedBillingCodes As C
ON C.BillingAccountKey = A.BillingAccountKey
And C.RN = 1
WHERE
A.AccountType = 'O'
;

ROW_NUMBER (Transact-SQL) | Microsoft Docs[^]

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[Paket Wisata Lombok]

sorry its very complex to me.