Struggling with a group by/partition by

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

Transact-SQL (2008)

Struggling with a group by/partition by

Author

Topic

theboyholty
Posting Yak Master

226 Posts

Posted - 2011-03-30 : 10:03:13

Please take a look at the code, you can paste it into SSMS for testing if you like. . .

CREATE TABLE #Contacts (
CompanyID INT,
ContactID INT,
JobRoleScore INT)

INSERT INTO #Contacts (CompanyID,ContactID,JobRoleScore) VALUES (1,1,3)
INSERT INTO #Contacts (CompanyID,ContactID,JobRoleScore) VALUES (1,2,2)
INSERT INTO #Contacts (CompanyID,ContactID,JobRoleScore) VALUES (1,3,1)
INSERT INTO #Contacts (CompanyID,ContactID,JobRoleScore) VALUES (2,4,5)
INSERT INTO #Contacts (CompanyID,ContactID,JobRoleScore) VALUES (3,5,5)
INSERT INTO #Contacts (CompanyID,ContactID,JobRoleScore) VALUES (3,6,4)
INSERT INTO #Contacts (CompanyID,ContactID,JobRoleScore) VALUES (3,7,3)
INSERT INTO #Contacts (CompanyID,ContactID,JobRoleScore) VALUES (3,8,2)
INSERT INTO #Contacts (CompanyID,ContactID,JobRoleScore) VALUES (3,9,1)
INSERT INTO #Contacts (CompanyID,ContactID,JobRoleScore) VALUES (4,10,5)
INSERT INTO #Contacts (CompanyID,ContactID,JobRoleScore) VALUES (4,11,4)
INSERT INTO #Contacts (CompanyID,ContactID,JobRoleScore) VALUES (4,12,3)
INSERT INTO #Contacts (CompanyID,ContactID,JobRoleScore) VALUES (4,13,2)
INSERT INTO #Contacts (CompanyID,ContactID,JobRoleScore) VALUES (5,14,1)
INSERT INTO #Contacts (CompanyID,ContactID,JobRoleScore) VALUES (5,15,0)
INSERT INTO #Contacts (CompanyID,ContactID,JobRoleScore) VALUES (5,16,2)
INSERT INTO #Contacts (CompanyID,ContactID,JobRoleScore) VALUES (5,17,4)
INSERT INTO #Contacts (CompanyID,ContactID,JobRoleScore) VALUES (5,18,3)
INSERT INTO #Contacts (CompanyID,ContactID,JobRoleScore) VALUES (5,19,1)
INSERT INTO #Contacts (CompanyID,ContactID,JobRoleScore) VALUES (5,20,4)

So my problem is thus:
I've got a list of companies, and with each company I've got a list of contacts. there can be many contacts per company and each one has a job role. As i'm looking for IT contacts, then Managing directors etc I've given each role a score and wish to find for each company the contact with the highest JobRoleScore.

So these are the results i'd expect based on the above data:
CompanyID 1, ContactID 1 (JobRoleScore 3)
CompanyID 2, ContactID 4 (JobRoleScore 5)
CompanyID 3, ContactID 5 (JobRoleScore 5)
CompanyID 4, ContactID 10 (JobRoleScore 5)
CompanyID 5, ContactID 20 (JobRoleScore 4) [there are 2 contacts with JobRoleScorescore of 4 so select higher ContactID]

I tried using GROUP BY and PARTITION BY but couldn't get those to work so may have to resort to some clever code. By the way, I'll be JOINing this to the companies table on the CompanyID field.

Any assistance would be very much appreciated.

Thanks guys.

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk The official unofficial website of Bury Football Club

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-30 : 10:19:06

Something like this?

with cte as
(
   select
      *,
      --dense_rank() over (partition by companyid order by jobrolescore desc) as rnk
      row_number() over (partition by companyid order by jobrolescore desc, contactId desc) as rnk
   from #Contacts
)
select * from cte where rnk = 1

But if you ask me (and you didn't :-), it is really unfair to give the job to someone just because their contact Id is higher. So if you want to pick both, uncomment the line that starts with dense_rank

theboyholty
Posting Yak Master

226 Posts

Posted - 2011-03-30 : 10:33:35

It's bloody brilliant that mate.

I really only need one contact per company so i'd decided that the one with the higher contactID would be the more current and therefore accurate. That said, I've never used dense_rank before so that's really helpful too.

Many thanks.

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk The official unofficial website of Bury Football Club

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources