SQLTeam.com | Weblogs | Forums

Select row(s) with the highest count

Transact-SQL
#1

Hi Guys,

If I have a Staff table that denotes staff JobDescriptions, how do I go about pulling out just the most commonly occurring JobDescription row(s)? Currently the table looks like:

JobDescription
Sysadmin
Sysadmin
Sysadmin
Sales Manager
Sales Manager
Middle Manager
Middle Manager
Middle Manager

By doing the following:

Select Staff.JobDescription,count(Staff.JobDescription) as JobCount from HR.Staff
group by Staff.JobDescription
order by JobCount desc;

I can grab a table like so:

JobDescription JobCount
Sysadmin 3
Middle Manager 3
Sales Manager 2

But my desired output is the single most commonly occuring JobDescription (which in this example has two both with a value of '3'):

JobDescription JobCount
Sysadmin 3
Middle Manager 3

Hopefully this isn't immediately obvious... I'm guessing it's going to involve a nested SELECT, in which case I'll forgive myself for feeling so lost.

Thanks!

#2

Hi

I am trying to understand this

If you have

Job. Count
ABC. 3
Def. 2
Ghj. 2
Ml. 1

Would expected output be
Def. 2
Ghj. 2

Thanks

1 Like
#3

Hello @Beginner,

Try this:
CONSUMABLE TABLE

Create TABLE #temp(
	JobDescription VARCHAR(MAX)
)
INSERT INTO #temp VALUES('Sysadmin')
INSERT INTO #temp VALUES('Sysadmin')
INSERT INTO #temp VALUES('Sysadmin')
INSERT INTO #temp VALUES('Sales Manager')
INSERT INTO #temp VALUES('Sales Manager')
INSERT INTO #temp VALUES('Middle Manager')
INSERT INTO #temp VALUES('Middle Manager')
INSERT INTO #temp VALUES('Middle Manager')

QUERY

with cte as(
	Select JobDescription, count(JobDescription) as JobCount from #temp
	group by JobDescription

)
SELECT * FROM cte WHERE JobCount IN (SELECT MAX(JobCount) FROM cte)

Result
Capture

Hope it's help You :smiley: :smile:

1 Like
#4

Yep, that's correct.

I did just manage to get it working with the RANK window function. Not the simplest solution I'm sure, but it works nonetheless!

@jacky.3311711027, thanks! That's brilliant, and a lot cleaner than what I hacked together.

#5

My Pleasure :smile: :blush:

#6

hi

i know this is from a while ago ..!!!!!!

i tried to do this
i love any feedback :slight_smile: :slight_smile:

drop create data ... 
DROP TABLE #temp 

go 

CREATE TABLE #temp 
  ( 
     jobdescription VARCHAR(max) 
  ) 

go 

INSERT INTO #temp 
VALUES     ('Sysadmin') 

INSERT INTO #temp 
VALUES     ('Sysadmin') 

INSERT INTO #temp 
VALUES     ('Sales Manager') 

INSERT INTO #temp 
VALUES     ('Sales Manager') 

INSERT INTO #temp 
VALUES     ('Middle Manager') 

INSERT INTO #temp 
VALUES     ('Middle Manager') 

INSERT INTO #temp 
VALUES     ('Middle Manager') 

go 

SELECT * 
FROM   #temp 

go
SQL ... mine ... different from jackys .. 
;WITH cte 
     AS (SELECT jobdescription, 
                Count(jobdescription) AS JobCount 
         FROM   #temp 
         GROUP  BY jobdescription), 
     cte123 
     AS (SELECT jobcount, 
                Count(jobcount) AS cntjobcount 
         FROM   cte 
         GROUP  BY jobcount), 
     cte345 
     AS (SELECT TOP 1 * 
         FROM   cte123 
         ORDER  BY cntjobcount DESC) 
SELECT b.* 
FROM   cte345 a 
       JOIN cte b 
         ON a.jobcount = b.jobcount 

go

image