id    start_date    stop_date

1      2009-01-01   2009-02-01
1      2009-01-02   2009-02-05
2      2009-01-02   null
3      2009-02-05   2009-03-02
4      2009-02-15   2009-04-01
4      2009-01-31   2009-03-01

declare @begin datetime ,@end datetime
set @begin='2009-01-01'
set @end='2009-03-01'
select * from tb t
where not exists(select * from tb where t.id=id and t.start_date<start_date)
and between @begin and @end datetime

------解决方案--------------------
SQL code
select id ,start_date=max(start_date),stop_date=max(stop_date) 
from tb
group by id
order by id

------解决方案--------------------

SQL code
declare @begin datetime ,@end datetime 
 set @begin='2009-01-01' 
 set @end='2009-03-01' 
 select * from tb t 
 where not exists(select * from tb where t.id=id and t.start_date <start_date) 
 and  start_date>=@begin and end_date <=@end 


------解决方案--------------------
SQL code
SQL code--按某一字段分组取最大(小)值所在行的数据
(爱新觉罗.毓华 2007-10-23于浙江杭州)
/*
数据如下：
name val memo
a    2   a2(a的第二个值)
a    1   a1--a的第一个值
a    3   a3:a的第三个值
b    1   b1--b的第一个值
b    3   b3:b的第三个值
b    2   b2b2b2b2
b    4   b4b4
b    5   b5b5b5b5b5
*/
--创建表并插入数据：
create table tb(name varchar(10),val int,memo varchar(20))
insert into tb values('a',    2,   'a2(a的第二个值)')
insert into tb values('a',    1,   'a1--a的第一个值')
insert into tb values('a',    3,   'a3:a的第三个值')
insert into tb values('b',    1,   'b1--b的第一个值')
insert into tb values('b',    3,   'b3:b的第三个值')
insert into tb values('b',    2,   'b2b2b2b2')
insert into tb values('b',    4,   'b4b4')
insert into tb values('b',    5,   'b5b5b5b5b5')
go

--一、按name分组取val最大的值所在行的数据。
--方法1：
select a.* from tb a where val = (select max(val) from tb where name = a.name) order by a.name
--方法2：
select a.* from tb a where not exists(select 1 from tb where name = a.name and val > a.val)
--方法3：
select a.* from tb a,(select name,max(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
--方法4：
select a.* from tb a inner join (select name , max(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
--方法5
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val > a.val ) order

------解决方案--------------------
SQL code
declare @tb table(id int,  start_date  varchar(10),   stop_date varchar(10))


insert @tb select 1 ,     '2009-01-01' ,  '2009-02-01'
insert @tb select 1 ,     '2009-01-02' ,  '2009-02-05'
insert @tb select 2 ,     '2009-01-02' ,  null
insert @tb select 3 ,     '2009-02-05' ,  '2009-03-02'
insert @tb select 4 ,     '2009-02-15' ,  '2009-04-01'
insert @tb select 4 ,     '2009-01-31' ,  '2009-03-01'


select id ,start_date=max(start_date),stop_date=max(stop_date) 
from @tb
group by id
order by id
/*
id          start_date stop_date  
----------- ---------- ---------- 
1           2009-01-02 2009-02-05
2           2009-01-02 NULL
3           2009-02-05 2009-03-02
4           2009-02-15 2009-04-01

（所影响的行数为 4 行）

------解决方案--------------------