求句MAX的SQL语句
表     A
Order             Date
001                 2007－1－1
001                 2007－1－1
001                 2007－1－2
001                 2007－1－2  
002                 2007－1－6
002                 2007－1－6
002                 2007－1－8
002                 2007－1－8
要的结果是
Order             Date
001                 2007－1－2
001                 2007－1－2  
002                 2007－1－8
002                 2007－1－8
急啊，新人求解


------解决方案--------------------
create table A( [Order] nvarchar(10), [Date] nvarchar(20))
insert into a select '001 ', '2007-1-1 '
insert into a select '001 ' , '2007-1-1 '
insert into a select '001 ' , '2007-1-2 '
insert into a select '001 ' , '2007-1-2 '
insert into a select '002 ' , '2007-1-6 '
insert into a select '002 ' , '2007-1-6 '
insert into a select '002 ' , '2007-1-8 '
insert into a select '002 ' , '2007-1-8 '

select * from a
where not exists (select 1 from a b where a.[Order]=b.[Order] and a.[Date] <b.[Date])

Order Date
---------- --------------------
001 2007-1-2
001 2007-1-2
002 2007-1-8
002 2007-1-8

(4 row(s) affected)


------解决方案--------------------
---方法1
Select * From A As T Where Not Exists
(Select 1 From A Where [Order]=T.[Order] And [Date]> T.[Date])
Order By [Order],[Date]
---方法2
Select * From A Where [Date] In
(Select Max([Date]) From A Group By [Order])
Order By [Order],[Date]
------解决方案--------------------
create table tb([Order] varchar(10),Date datetime)
insert into tb values( '001 ', '2007-1-1 ')
insert into tb values( '001 ', '2007-1-1 ')
insert into tb values( '001 ', '2007-1-2 ')
insert into tb values( '001 ', '2007-1-2 ')
insert into tb values( '002 ', '2007-1-6 ')
insert into tb values( '002 ', '2007-1-6 ')
insert into tb values( '002 ', '2007-1-8 ')
insert into tb values( '002 ', '2007-1-8 ')
go
select * from tb where date in (select max(date) from tb group by [order])
drop table tb

/*
Order Date
---------- -----------------------
001 2007-01-02 00:00:00.000
001 2007-01-02 00:00:00.000
002 2007-01-08 00:00:00.000
002 2007-01-08 00:00:00.000

（所影响的行数为 4 行）
*/