Many-to-one: Displaying one of the many per one

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

General SQL Server Forums

New to SQL Server Programming

Many-to-one: Displaying one of the many per one

Author

Topic

MrQuizzles
Starting Member

20 Posts

Posted - 2010-01-12 : 14:13:15

I have two tables with the following columns:

client
-id
-companyname

contacts
-id
-clientId
-name
-email
-phone
-priority

contacts has a many-to-one relationship with client; there are many contacts for each client.
The two relate on contacts.clientId = client.id

What I need to do is display the information from client along with the information for the contact with the highest priority for each client. If two contacts have the same priority within a client (I'm not enforcing uniqueness on it), then arbitrarily choose one of them.

We can assume that each client will have at least one contact, but bonus points for being able to display clients without associated contacts (it SHOULD be impossible to create such a situation without directly accessing the database and manually making those changes, but let's say just in case) .

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-12 : 14:20:34

Try this..

select a.id
,a.companyname
,b.name
,b.email
,b.phone
,b.priority
from client a
left join (select row_number() over(partition by clientId order by priority desc) as seq, * from contacts) b
on a.id = b.clientId and b.seq = 1

MrQuizzles
Starting Member

20 Posts

Posted - 2010-01-12 : 14:31:33

That works, thanks.

I guess I need to learn how partitioning works. I've never come across it before, but it seems quite useful.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources