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 BillingAccountKeyUNION ALLSELECT 456, 'O', 2UNION ALLSELECT 789, 'O', 3UNION ALLSELECT 101, 'I', 4UNION ALLSELECT 102, 'O', 5) ASELECT * FROM #UserAccount
--================================================================
SELECT * INTO #BillingTransactions FROM (SELECT 1 AS BillingTransactionKey,1 AS BillingAccountKey,111 AS BillingCodeUNION ALLSELECT 2,2,222UNION ALLSELECT 3,2,333UNION ALLSELECT 4,2,444UNION ALLSELECT 5,3,111UNION ALLSELECT 6,3,555UNION ALLSELECT 7,3,666UNION ALLSELECT 8,3,222UNION ALLSELECT 9,5,333UNION ALLSELECT 10,5,777)ASELECT * FROM #BillingTransactions
--===============================================
SELECT * INTO #BillingCodeRank FROM(SELECT 111 AS BillingCode,7 AS [Rank]UNION ALLSELECT 222,6UNION ALLSELECT 333,5UNION ALLSELECT 444,4UNION ALLSELECT 555,3UNION ALLSELECT 666,2UNION ALLSELECT 777,1UNION ALLSELECT 888,1UNION ALLSELECT 999,3UNION ALLSELECT 101,5)ASELECT * FROM #BillingCodeRank