继续SQL小问题，基础题，速结贴
首先我不是拿来主义，只是问题在这里问总比在搜索引擎来的有针对性。
有一个表，
ID productname createdate
1 A1 2013-02-01 10:10:10
2 A1 2013-02-01 10:20:10
3 A2 2013-02-01 10:32:10
4 A3 2013-02-01 10:45:10
我想得到的数据是
ID productname createdate
2 A1 2013-02-01 10:20:10
3 A2 2013-02-01 10:32:10
4 A3 2013-02-01 10:45:10
该如何过滤叼第一项呢，通过createdate
SQL
[解决办法]
select *from [Table] a where not exists(select 1 from [Table] where productname=a.productname and createdate>a.createdate)
[解决办法]
select * from tb a where exists
(select 1 from tb where a.a1=a1 group by productname
having max(createdate)=a.createdate)
[解决办法]
----------------------------
-- Author :DBA_Huangzj()
-- Date :2013-02-19 17:11:36
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (Intel X86)
--Jun 17 2011 00:57:23
--Copyright (c) Microsoft Corporation
--Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([ID] int,[productname] varchar(2),[createdate] datetime)
insert [huang]
select 1,'A1','2013-02-01 10:10:10' union all
select 2,'A1','2013-02-01 10:20:10' union all
select 3,'A2','2013-02-01 10:32:10' union all
select 4,'A3','2013-02-01 10:45:10'
--------------开始查询--------------------------
SELECT *
FROM [huang] a
WHERE EXISTS ( SELECT 1
FROM ( SELECT [productname] ,
MAX([createdate]) [createdate]
FROM huang
GROUP BY [productname]
) b
WHERE a.[productname] = b.[productname]
AND a.createdate = b.createdate )
----------------结果----------------------------
/*
ID productname createdate
----------- ----------- -----------------------
2 A1 2013-02-01 10:20:10.000
3 A2 2013-02-01 10:32:10.000
4 A3 2013-02-01 10:45:10.000
(3 行受影响)
*/