query help needed ASAP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

query help needed ASAP

Author

Topic

sreenu9f
Yak Posting Veteran

73 Posts

Posted - 2010-06-16 : 18:25:06

I have a table like this

AcctNo Code Weight Amount
123 pxx 4.5 120
123 pxy 4.3 100
234 a23 1.2 50
234 b13 1.2 25
456 c13 2.2 50
456 d32 2.2 50

I would like to have only one Code for every given AcctNo based on the logic
1) check the weights and if the weights are different pick the code with the highest weight
2) if the weights are same pick the code with the highest Amount
3) if the weights and the Amount columns are same for an account then pick the first code.
The result should look like this.

AcctNo Code
123 pxx
234 a23
456 c13

please suggest. i need this ASAP.

Thanks,

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-06-16 : 18:34:50

[code]select AcctNo, Code from (
select *, row_number() over (partition by acctno order by acctno, weight desc) r
from myTable ) a
where r=1[/code]

matty
Posting Yak Master

161 Posts

Posted - 2010-06-17 : 01:55:58

a small change to meet condition 2

quote:

Originally posted by robvolk

select AcctNo, Code from (
 select *, row_number() over (partition by acctno order by acctno, weight desc,amount desc) r 
 from myTable ) a
where r=1

sreenu9f
Yak Posting Veteran

73 Posts

Posted - 2010-06-18 : 14:31:57

Thank u all. It was very helpful.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources