MAX of COUNT with associated field (SOLVED!)

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)

MAX of COUNT with associated field (SOLVED!)

Author

Topic

ReedM
Starting Member

2 Posts

Posted - 2011-02-22 : 15:42:14

Example Table & Data


Name             JobTitle         GroupID          SubGroupID

Han Solo         Pilot            1                A
Pricess Leia     Politician       1                A
Chewbacca        Pilot            1                A
Riggs            Pilot            1                A
Luke Skywalker   Jedi Knight      1                B
Yoda             Jedi Knight      1                B
Darth Vader      Sith Lord        1                B
Boba Fett        Bounty Hunter    2                A
Bossk            Bounty Hunter    2                A

I need to group on a field alias ((GroupID+SubGroupID) AS JobCat), count the number of times a JobTitle appears in each JobCat group, then return the JobTitle and JobCat for only the JobTitle that has the max count for that group.

Example Query Return


JobTitle          JobCat

Pilot             1A
Jedi Knight       1B
Bounty Hunter     2A

I have figured out how to return the count of the max occurring JobTitle and the JobCat, but not the associated JobTitle.


select MAX(TitleCount) AS TitleCount, (GroupID+SubGroupID) AS JobCat
from (
  SELECT JobTitle, COUNT(JobTitle) AS TitleCount FROM ExampleTable GROUP BY JobTitle
) as x inner join ExampleTable as f on f.JobTitle = x.JobTitle GROUP BY (GroupID+SubGroupID) ORDER BY (GroupID+SubGroupID);

Any help would be greatly appreciated.

Thanks,

Reed

singularity
Posting Yak Master

153 Posts

Posted - 2011-02-22 : 19:52:59

[code]
select JobTitle, JobCat
from
(select GroupID+SubGroupID as JobCat, JobTitle,
row_number() over (partition by GroupID+SubGroupID order by count(JobTitle) desc) as rn
from ExampleTable
group by GroupID+SubGroupID, JobTitle)
where rn = 1
[/code]

ReedM
Starting Member

2 Posts

Posted - 2011-02-23 : 09:14:21

Made some very minor changes, and it worked flawlessly:


select JobTitle, JobCat
from
(select GroupID+SubGroupID as JobCat, JobTitle, 
       row_number() over (partition by GroupID+SubGroupID order by count(JobTitle) desc) as rn
from ExampleTable
group by GroupID+SubGroupID, JobTitle) as t
where t.rn = 1

That was amazing, singularity!

Thanks,

Reed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources