There must be a way to do this simply. We're running SQL Server 2000. I'm looking for some generic SQL statement that I can apply.
If I have a table with a person column and a location column and multiple records for the same person / locatioin combination, how do I select the person with the location they most frequently visited? Say George visits Mexico 5 times, and the Bahamas twice and costa rica once. I would have 8 records in my table for George. The data looks something like this:
George/Mexico
George/Mexico
George/Mexico
George/Mexico
George/Mexico
George/Bahamas
George/Bahamas
George/Costa Rica
Ben/Brazil
Ben/Brazil
Ben/Peru
The results would be:
George/Mexico
Ben/Brazil
Thanks!
Myles
With SQL Server 2005, it's fairly simple. Here's an example using the Northwind sample database, since you didn't provide your table definition:with T(CustomerID, EmployeeID, rk) as (
select
CustomerID,
EmployeeID,
row_number() over (partition by CustomerID order by count(*) desc)
from Northwind..Orders
group by CustomerID, EmployeeID
)
select
CustomerID,
EmployeeID
from T
where rk = 1
If you want to include ties for most frequent location per person, use rank() instead of row_number().
With SQL Server 2000, it's a little less efficient - the same query would look like this:
select
CustomerID,
EmployeeID
from Northwind..Orders as O1
where CustomerID like 'A%'
group by CustomerID, EmployeeID
having count(*) = (
select max(ct) from (
select count(*) as ct
from Northwind..Orders as O2
where O2.CustomerID = O1.CustomerID
group by CustomerID, EmployeeID
) as Cts
)
Steve Kass
Drew University
www.stevekass.com|||
create table visits
(
visit varchar (200)
)
insert visits(visit) values ('George/Mexico')
insert visits(visit) values ('George/Mexico')
insert visits(visit) values ('George/Mexico')
insert visits(visit) values ('George/Mexico')
insert visits(visit) values('George/Mexico')
insert visits(visit) values('George/Mexico')
insert visits(visit) values ('George/Bahamas')
insert visits(visit) values ('George/Bahamas')
insert visits(visit) values ('George/Costa Rica')
insert visits(visit) values ('Ben/Brazil')
insert visits(visit) values('Ben/Brazil')
insert visits(visit) values ('Ben/Peru')
select left(visit,(CHARINDEX('/',visit,0)-1))AS person,
right(visit,len(visit)-1-len(left(visit,(CHARINDEX('/',visit,0)-1)))) as places
into #tempx
from visits
select * from #tempx
select person , places, count(*) as counts into #tempy from #tempx
group by person, places
order by person,counts desc
select a.person,b.places, a.counts from
(
selecT person, max(counTs)as counts from #tempy group by person )
as
a
join
(
select * from #tempy
) as b
on a.person=b.person and a.counts=b.counts
--drop table visits
--drop table #tempx
--drop table #tempy
