Comparing row counts and displaying results from greatest row count from a single table

Question

Post reply

Comparing row counts and displaying results from greatest row count from a single table

gregorykearney

Old Hand

Points: 304
More actions
August 26, 2013 at 8:47 pm

#281718

insert into CallData ([800num], CompanyName) values ('8009874321', 'cars');
insert into CallData ([800num], CompanyName) values ('8009874321', 'Newsales');
insert into CallData ([800num], CompanyName) values ('8009874321', 'Newsales');
insert into CallData ([800num], CompanyName) values ('8009870000', 'BenaSales');
insert into CallData ([800num], CompanyName) values ('8009870000', 'BenaSales');
insert into CallData ([800num], CompanyName) values ('8009870000', 'BenaSales2');
insert into CallData ([800num], CompanyName) values ('8009870000', 'BenaSales2');
Requirement 1: When the 800num matches I would like to return a single row, the row with the greatest row count. Requirement 2: If the 800num row count matches then return both rows.
Results would be:
8009874321 Newsales
8009870000 BenaSales
8009870000 BenaSales2

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Andrew G SSChampion Points: 12809 More actions · Answer 1

How about this?

select max(x.row) rows, x.[800num], x.CompanyName

from (

select row_number() OVER (partition by a.CompanyName order by a.[800num]) row, a.[800num], a.CompanyName

from calldata a ) as x

group by x.[800num], x.CompanyName

gregorykearney Old Hand Points: 304 More actions · Answer 2

Thanks for the reply, I only want the results to show the 800 number with the greatest row count.

gregorykearney Old Hand Points: 304 More actions · Answer 3

Here's what I've come up with:

select distinct [800number], (

select top 1 CompanyName

from calldata

where calldata.[800number]=cd.[800number]

group by [800number], CompanyName

order by [800number], count(*) desc) as companyname

from calldata cd

My issue is this solution doesn't list companyname that have matching row counts. Meaning is 8001231234 "newcars" as 3 rows and 8001231234 "cars" has 3 rows only one of those will be listed. I wanted both of them listed because they have the same 800 number - 8001231234. Any ideas how to rewrite the query to accomplish this requirement?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

;WITH SampleData AS (

SELECT * FROM (values

('8009874321', 'cars'),

('8009874321', 'Newsales'),

('8009870000', 'Newsales'),

('8009870000', 'BenaSales'),

('8009870000', 'BenaSales2'),

('8009870000', 'BenaSales2')

) sampleData ([800num], CompanyName))

SELECT [800num], CompanyName

FROM (

SELECT [800num], CompanyName,

dr = DENSE_RANK() OVER(PARTITION BY [800num] ORDER BY cnt DESC)

FROM (

SELECT [800num], CompanyName, cnt = COUNT(*)

FROM SampleData

GROUP BY [800num], CompanyName

) d

) e

WHERE dr = 1

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

gregorykearney Old Hand Points: 304 More actions · Answer 5

gregorykearney

Old Hand

Points: 304

August 27, 2013 at 9:54 am

#1645261

Amazing how fast this query works...Thank you!