-- prepare sample data
create table	#sample
		(
			a varchar(3),
			t1 smalldatetime,
			t2 smalldatetime,
			b smallint
		)

insert	#sample
select	'020', '20060301', '20091231', 11113 union all
select	'020', '20051101', '20051130', 11113 union all
select	'020', '20060101', '20060131', 11113 union all
select	'0X0', '20051201', '20051231', 11113 union all
select	'020', '20060203', '20060228', 11113 union all
select	'020', '20100101', ' 20700101', 11113

-- set up sample date in order
create unique clustered index ix_sample on #sample (t1, a)
alter table #sample add grp int
go

-- update grp#
declare	@grp int,
	@last varchar(3)

update	#sample
set	@grp = grp = case when isnull(@last, '') <> a then isnull(@grp, 0) + 1 else @grp end,
	@last = a

-- show the expected output
select		a,
		min(t1) as t1,
		max(t2) as t2
from		#sample
group by	a,
		grp
order by	2

alter table #sample drop column grp
go

select * from #sample

drop table #sample

quote:

---

Originally posted by facestheband

You may not need a group by here at all if you don't need a count.


write the following
select distinct field1, field2, field3 from tablename

if you need a count at the end of each row then write

select field1, field2, field3, count(*) from tablename group by field1, field2, field3

---