August 26, 2013 at 8:47 pm
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
August 26, 2013 at 9:21 pm
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
August 26, 2013 at 9:35 pm
Thanks for the reply, I only want the results to show the 800 number with the greatest row count.
August 26, 2013 at 10:17 pm
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?
August 27, 2013 at 3:35 am
;WITH SampleData AS (
SELECT * FROM (values
('8009874321', 'cars'),
('8009874321', 'Newsales'),
('8009874321', 'Newsales'),
('8009870000', 'Newsales'),
('8009870000', 'BenaSales'),
('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
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
August 27, 2013 at 9:54 am
Amazing how fast this query works...Thank you!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply