create table t(rectime datetime,fieldA nvarchar(01))
insert into t
select '2006-07-10','A' union all
select '2006-07-11','A' union all
select '2006-08-10','A' union all
select '2006-08-11','B' union all
select '2006-08-12','B'
select convert(char(07),rectime,120) as FResultA,FieldA as FResultB,
count(*) as num
into #t from t
group by convert(char(07),rectime,120) ,FieldAselect a.* from #t a inner join
(select FResultA,max(num) as num from #t group by FResultA ) b
on a.FResultA=b.FResultA and a.num=b.numdrop table t
drop table #t
FResultA FResultB num
-------- -------- -----------
2006-07 A 2
2006-08 B 2
insert into t
select '2006-07-10','A' union all
select '2006-07-11','A' union all
select '2006-08-10','A' union all
select '2006-08-11','B' union all
select '2006-08-12','B'
select convert(char(07),rectime,120) as FResultA,FieldA as FResultB,
count(*) as num
into #t from t
group by convert(char(07),rectime,120) ,FieldAselect a.* from #t a inner join
(select FResultA,max(num) as num from #t group by FResultA ) b
on a.FResultA=b.FResultA and a.num=b.numdrop table t
drop table #t
FResultA FResultB num
-------- -------- -----------
2006-07 A 2
2006-08 B 2
insert into t
select '2006-07-10','A' union all
select '2006-07-11','A' union all
select '2006-08-10','A' union all
select '2006-08-11','B' union all
select '2006-08-11','B' union all
select '2006-09-11','A' union all
select '2006-10-11','C' union all
select '2006-10-11','C' union all
select '2006-10-11','C' union all
select '2006-10-11','A' select convert(char(07),rectime,120) as FResultA,FieldA as FResultB,
count(*) as num
into #t from t
group by convert(char(07),rectime,120) ,FieldAselect a.* from #t a inner join
(select FResultA,max(num) as num from #t group by FResultA ) b
on a.FResultA=b.FResultA and a.num=b.num
order by a.FResultAdrop table t
drop table #t/*result:*/
FResultA FResultB num
-------- -------- -----------
2006-07 A 2
2006-08 B 2
2006-09 A 1
2006-10 C 3
insert into t
select '2006-07-10','A' union all
select '2006-07-11','A' union all
select '2006-08-10','A' union all
select '2006-08-11','B' union all
select '2006-08-12','B'
select convert(char(07),rectime,120) as FResultA,FieldA as FResultB,
count(*) as num
into #t from t
group by convert(char(07),rectime,120) ,FieldAselect a.* from #t a inner join
(select FResultA,max(num) as num from #t group by FResultA ) b
on a.FResultA=b.FResultA and a.num=b.numdrop table t
drop table #t
insert @a select '2006-7-10','A'
union all select '2006-7-20', 'A'
union all select '2006-8-10', 'A'
union all select '2006-8-10', 'B'
union all select '2006-8-10', 'B'
union all select '2006-9-5', 'A'
union all select '2006-10-10', 'C'
union all select '2006-10-10', 'C'
union all select '2006-10-10', 'C'
union all select '2006-10-10', 'A'select sj,fielda,count(1) sl into aaaa from (select convert(varchar(7),RecTime,120) sj,fielda from @a) a group by sj,fielda
select * from aaaa a where not exists(select 1 from aaaa where sj=a.sj and sl>a.sl)