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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy