id dateyear
1 2007-05-06
2 2007-07-16
3 2007-07-16
先查dateyear中是大的.如果有一条查询结果就不再职行另一个条件.如果有两条以上的查询结果.然后就根据dateyear中查询的结果取出id中最大的
1 2007-05-06
2 2007-07-16
3 2007-07-16
先查dateyear中是大的.如果有一条查询结果就不再职行另一个条件.如果有两条以上的查询结果.然后就根据dateyear中查询的结果取出id中最大的
select @date=max(dateyear)
select top 1 * from table1 where dateyear=@date
select 1, '2007-05-06'
union select 2 , '2007-07-16'
union select 3 , '2007-07-16'--1、
select max(id),dateyear from (
select * from tt t where exists (select 1 from tt where t.dateyear>dateyear) ) d
group by dateyear--2、
select top 1 max(id),dateyear from tt group by dateyear order by dateyear desc
drop table tt
insert tt
select 1, '2007-05-06'
union select 2 , '2007-07-16'
union select 3 , '2007-07-16'--1、
select max(id),dateyear from (
select * from tt t where exists (select 1 from tt where t.dateyear>dateyear) ) d
group by dateyear--2、
select top 1 max(id),dateyear from tt group by dateyear order by dateyear desc
drop table tt
select top 1 * from t a
left join
(
select top 1 max(dateyear)as dateyear from t
) b on a.dateyear=b.dateyear
order by id desc
----------- ------------------------------------------------------ ------------------------------------------------------
3 2007-07-16 00:00:00.000 2007-07-16 00:00:00.000(1 row(s) affected)
left join
(
select top 1 max(dateyear)as dateyear from t
) b on a.dateyear=b.dateyear
order by id descid dateyear
----------- ------------------------------------------------------
3 2007-07-16 00:00:00.000(1 row(s) affected)
(
id int identity(1,1) not null,
dt datetime not null
)
insert into @tab (dt) values('2007-05-06')
insert into @tab (dt) values('2007-07-16')
insert into @tab (dt) values('2007-07-16')select * from @tabselect max(a.id)
from (select * from @tab where dt = (select max(dt) from @tab)) a