January 10, 2006 at 1:21 pm
Here's the data:
create table #temp(
field1 varchar(100),
field2 varchar(100))
insert into #temp values ('abc','123')
insert into #temp values ('abc','123')
insert into #temp values ('mno','123')
insert into #temp values ('xyz','123')
insert into #temp values ('rrr','123')
insert into #temp values ('ggg','789')
insert into #temp values ('abc','789')
insert into #temp values ('ggg','789')
insert into #temp values ('xyz','789')
insert into #temp values ('ggg','789')
insert into #temp values ('qqq','456')
insert into #temp values ('qqq','456')
insert into #temp values ('qqq','456')
insert into #temp values ('qqq','456')
insert into #temp values ('ggg','456')
How do I get the following result set:
field1 field2
abc 123
ggg 789
qqq 456
The idea is that I want to get the field1 and field2 for each field2 that has the most entries of field1 for each field2.
I know I've done this before but apparently I've lost my mind.
Who can solve this the quickest?
Thanks,
hawg
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
January 10, 2006 at 2:02 pm
select field1, count(field1+field2), field2 from #temp
group by field1, field2
having count(field1+field2)>1
HTH
January 10, 2006 at 2:14 pm
Sorry, I gave bad sample data. Try the following data:
insert into #temp values ('abc','123')
insert into #temp values ('abc','123')
insert into #temp values ('mno','123')
insert into #temp values ('xyz','123')
insert into #temp values ('mno','123')
insert into #temp values ('mno','123')
insert into #temp values ('mno','642')
insert into #temp values ('ggg','789')
insert into #temp values ('abc','789')
insert into #temp values ('ggg','789')
insert into #temp values ('xyz','789')
insert into #temp values ('ggg','789')
insert into #temp values ('qqq','456')
insert into #temp values ('qqq','456')
insert into #temp values ('qqq','456')
insert into #temp values ('qqq','456')
insert into #temp values ('ggg','456')
Using your query, this gives me:
abc 2 123
mno 3 123
qqq 4 456
ggg 3 789
Sorry for the confusion. Thanks anyway.
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply