if object_id('[tab]') is not null drop table [tab]
go
create table [tab]([id] int,[order_number] varchar(11),[order_time] datetime)
insert [tab]
select 1,'3841894','2012-05-19 19:40:02' union all
select 2,'3841894','2012-05-20 19:40:02' union all
select 3,'3841894','2012-05-20 19:50:02' union all
select 4,'3841894','2012-05-21 19:40:02' union all
select 5,'12345678912','2012-05-21 19:40:02' union all
select 6,'12345678912','2012-05-21 19:40:02' union all
select 7,'12345678912','2012-05-21 19:40:02' union all
select 8,'3H1gyBZNaq6','2012-05-25 20:22:01' union all
select 9,'BAy2pH5LEnk','2012-05-25 20:23:51'
go

select * from tab t
where not exists(select 1 from tab where order_number=t.order_number and id>t.id)
order by id desc

/**
id          order_number order_time
----------- ------------ -----------------------
9           BAy2pH5LEnk  2012-05-25 20:23:51.000
8           3H1gyBZNaq6  2012-05-25 20:22:01.000
7           12345678912  2012-05-21 19:40:02.000
4           3841894      2012-05-21 19:40:02.000

(4 行受影响)
**/

------解决方案--------------------
SQL code

create table Tab
(id int, order_number varchar(20), order_time datetime)

insert into Tab
select 1, '3841894', '2012-05-19 19:40:02' union all
select 2, '3841894', '2012-05-20 19:40:02' union all
select 3, '3841894', '2012-05-20 19:50:02' union all
select 4, '3841894', '2012-05-21 19:40:02' union all
select 5, '12345678912', '2012-05-21 19:40:02' union all
select 6, '12345678912', '2012-05-21 19:40:02' union all
select 7, '12345678912', '2012-05-21 19:40:02' union all
select 8, '3H1gyBZNaq6', '2012-05-25 20:22:01' union all
select 9, 'BAy2pH5LEnk', '2012-05-25 20:23:51'


with t as
(select row_number() over(partition by order_number order by id desc) rn,
 id,order_number,order_time from Tab
)
select id,order_number,order_time
from t where rn=1 order by id desc

/*
id          order_number         order_time
----------- -------------------- -----------------------
9           BAy2pH5LEnk          2012-05-25 20:23:51.000
8           3H1gyBZNaq6          2012-05-25 20:22:01.000
7           12345678912          2012-05-21 19:40:02.000
4           3841894              2012-05-21 19:40:02.000

(4 row(s) affected)
*/