sql query to get code with max rank

Question

Post reply

sql query to get code with max rank

dk98681

SSC-Addicted

Points: 497
More actions
February 27, 2018 at 10:36 am

#410277

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63503 More actions · Answer 1

Thanks for posting the INSERT scripts. But without a corresponding CREATE TABLE script, the data has nowhere to go.

curtw Old Hand Points: 335 More actions · Answer 2

No need for CREATE TABLE since there are SELECT INTO statements

Try this:
SELECT UserAccount, AccountType, BillingAccountKey, BillingCode, [Rank] FROM ( SELECT UserAccount = UA.UserAccounts, UA.AccountType, UA.BillingAccountKey, BR.BillingCode, BR.[Rank], RowNum = ROW_NUMBER() OVER(PARTITION BY UA.UserAccounts ORDER BY BR.[Rank] DESC) FROM #UserAccount UA INNER JOIN #BillingTransactions BT ON BT.BillingAccountKey = UA.BillingAccountKey INNER JOIN #BillingCodeRank BR ON BR.BillingCode = BT.BillingCode WHERE UA.AccountType = 'O' ) SUB WHERE SUB.RowNum = 1 ORDER BY UserAccount

sathwik.em91 SSC Eights! Points: 878 More actions · Answer 3

Hi All,
I am very bad at formatting,please dont mine.Let me know if it works for you!!
SELECT
                          QL.USERAccounts
                         , QL.Accounttype
                         , QL.Billingcode
                         , QL.Rank
                         --, Ql.seq

                        FROM
                          (SELECT
                                    USERAccounts
                                , Accounttype
                                , UA.BillingAccountKey
                                , BCR.Rank
                                , BCR.BillingCode
                                , ROW_NUMBER() OVER(PARTITION BY UserAccounts ORDER BY UserAccounts) as seq

                            FROM #UserAccount UA
                                                INNER JOIN #BillingTransactions BT ON UA.BillingAccountKey=BT.BillingAccountKey

                                                INNER JOIN #BillingCodeRank BCR ON BT.BillingCode=BCR.BillingCode

                            WHERE AccountType='O'
                            )AS QL

                        WHERE
                         seq=1
                        ORDER BY
   QL.UserAccounts

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

dk98681 - Tuesday, February 27, 2018 10:36 AM
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

Copy your SELECT INTO script from your post, paste it into SSMS, and let us know what you come up with. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

David Burrows SSC Guru Points: 65142 More actions · Answer 5

SELECT a.UserAccounts,a.AccountType,a.BillingAccountKey,b.BillingCode,b.[Rank] FROM #UserAccount a CROSS APPLY (SELECT TOP (1) t.BillingCode,r.[Rank] FROM #BillingTransactions t JOIN #BillingCodeRank r ON r.BillingCode = t.BillingCode WHERE t.BillingAccountKey = a.BillingAccountKey ORDER BY r.[Rank] DESC) b WHERE a.AccountType = 'O';

Far away is close at hand in the images of elsewhere.
Anon.

UserAccount	AccountType	BillingAccountKey	BillingCode	Rank
456	O	2	222	6
789	O	3	111	7
102	O	5	333	5