SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


sql query to get code with max rank


sql query to get code with max rank

Author
Message
dk98681
dk98681
SSC Veteran
SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)

Group: General Forum Members
Points: 276 Visits: 131

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:

UserAccountAccountTypeBillingAccountKey BillingCode Rank
456 O22226
789 O31117
102 O53335


--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


pietlinden
pietlinden
SSC-Forever
SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)

Group: General Forum Members
Points: 48824 Visits: 16131
Thanks for posting the INSERT scripts. But without a corresponding CREATE TABLE script, the data has nowhere to go.
curtw
curtw
SSC-Enthusiastic
SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)

Group: General Forum Members
Points: 111 Visits: 19
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
sathwik.em91
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 468
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
Jeff Moden
SSC Guru
SSC Guru (798K reputation)SSC Guru (798K reputation)SSC Guru (798K reputation)SSC Guru (798K reputation)SSC Guru (798K reputation)SSC Guru (798K reputation)SSC Guru (798K reputation)SSC Guru (798K reputation)

Group: General Forum Members
Points: 798171 Visits: 45954
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:

UserAccountAccountTypeBillingAccountKey BillingCode Rank
456 O22226
789 O31117
102 O53335


--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. Wink

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
David Burrows
David Burrows
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47938 Visits: 11158

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.


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search