Select the most use combo - 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)

Select the most use combo

Author

Topic

jhoeft
Starting Member

2 Posts

Posted - 2008-12-30 : 14:41:58

Hi!

i cant figure this one out... i am hoping someone has done this before?

i have a payment method table with a member-id, aba-number, and account number.

sample:
id member-id aba acct
1 11 702 1000
2 11 702 1000
3 11 702 1001
4 11 760 1000
5 71 705 3121

i want to write a query that will give me the most used aba-number account combo per member....

so it would return

11 702 1000
71 705 3121

any ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 14:46:19

[code]
SELECT member-id,aba, acct
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY member-id ORDER BY Cnt DESC) AS Seq,member-id, aba, acct
FROM
(SELECT member-id, aba, acct,COUNT(id) OVER (PARTITION BY member-id, aba, acct) AS Cnt
FROM YourTable)t
)r
WHERE Seq=1
[/code]

jhoeft
Starting Member

2 Posts

Posted - 2008-12-30 : 17:11:43

Thanks that is exactly what i needed....

i was unaware of the rank feature till now thanks again!

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2008-12-31 : 00:04:58

select memberid,aba,acct from
( select *,row_number() over( partition by memberid order by aba)as rn from payment ) as p
where rn = 1

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-31 : 00:08:15

select a.memberid,a.aba,a.acct from
(select memberid,aba,acct,row_number() over (partition by memberid order by count(id) desc) as rowno from payment group by memberid,aba,acct ) a
where rowno =1

Jai Krishna

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-31 : 02:59:45

quote:
Originally posted by Nageswar9

select memberid,aba,acct from
( select *,row_number() over( partition by memberid order by aba)as rn from payment ) as p
where rn = 1

where are you getting count value reqd for sorting?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-31 : 03:00:05

quote:
Originally posted by jhoeft

Thanks that is exactly what i needed....

i was unaware of the rank feature till now thanks again!

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources