create table tb(id int, date datetime) insert into tb values(1, '2003-5-3') insert into tb values(2, '2004-9-27') insert into tb values(3, '2005-12-31') insert into tb values(4, '2006-1-10') goselect top 2 * from tb order by date descdrop table tb/* id date ----------- ----------------------- 4 2006-01-10 00:00:00.000 3 2005-12-31 00:00:00.000(2 行受影响) */
id date 'A0456' '2003-5-3' 'Br151' '2006-8-31' 'c54t' '2004-9-27' 'B23t' '2005-12-31' ----- 结果 'B23t' '2005-12-31''Br151' '2006-8-31'
create table tb(id int, date datetime) insert into tb values(1, '2003-5-3') insert into tb values(2, '2004-9-27') insert into tb values(3, '2005-12-31') insert into tb values(4, '2006-1-10') goselect * from (select top 2 * from tb order by date desc) t order by datedrop table tb/* id date ----------- ----------------------- 3 2005-12-31 00:00:00.000 4 2006-01-10 00:00:00.000(2 行受影响) */
select top 2 * from tb where date <getdate() order by date desc
create table tb(id varchar(10), date datetime) insert into tb values('A0456' , '2003-5-3') insert into tb values('Br151' , '2006-8-31') insert into tb values('c54t' , '2004-9-27') insert into tb values('B23t' , '2005-12-31') goselect * from (select top 2 * from tb order by date desc) t order by datedrop table tb/* id date ---------- ----------------------- B23t 2005-12-31 00:00:00.000 Br151 2006-08-31 00:00:00.000(2 行受影响) */
insert into tb values(1, '2003-5-3')
insert into tb values(2, '2004-9-27')
insert into tb values(3, '2005-12-31')
insert into tb values(4, '2006-1-10')
goselect top 2 * from tb order by date descdrop table tb/*
id date
----------- -----------------------
4 2006-01-10 00:00:00.000
3 2005-12-31 00:00:00.000(2 行受影响)
*/
'A0456' '2003-5-3'
'Br151' '2006-8-31'
'c54t' '2004-9-27'
'B23t' '2005-12-31'
----- 结果 'B23t' '2005-12-31''Br151' '2006-8-31'
insert into tb values(1, '2003-5-3')
insert into tb values(2, '2004-9-27')
insert into tb values(3, '2005-12-31')
insert into tb values(4, '2006-1-10')
goselect * from (select top 2 * from tb order by date desc) t order by datedrop table tb/*
id date
----------- -----------------------
3 2005-12-31 00:00:00.000
4 2006-01-10 00:00:00.000(2 行受影响)
*/
insert into tb values('A0456' , '2003-5-3')
insert into tb values('Br151' , '2006-8-31')
insert into tb values('c54t' , '2004-9-27')
insert into tb values('B23t' , '2005-12-31')
goselect * from (select top 2 * from tb order by date desc) t order by datedrop table tb/*
id date
---------- -----------------------
B23t 2005-12-31 00:00:00.000
Br151 2006-08-31 00:00:00.000(2 行受影响)
*/
from t
order by [date] desc