过滤重复的
各位，请教:
tbl_abc,结构如下
使用 select id,pid,fullplaytime from tbl_schedule where datediff(hour,getdate(),fullplaytime)>2 order by pid desc
以下为符合条件的记录：
id pid fullplaytime
3252101022012-07-13 19:30:00.000
3253101022012-07-13 19:30:00.000
3254101022012-07-13 19:30:00.000
3303101022012-07-24 19:30:00.000
3304101022012-07-25 19:30:00.000
3305101022012-07-25 19:30:00.000
3306101022012-07-25 19:15:00.000
3307101022012-07-25 19:30:00.000
3308101022012-07-26 19:30:00.000
3309101022012-07-26 19:30:00.000
3310101022012-07-26 19:15:00.000
3311101022012-07-26 19:30:00.000
3312101022012-07-27 19:30:00.000
3313101022012-07-27 19:30:00.000
3314101022012-07-27 19:15:00.000
3315101022012-07-27 19:30:00.000
3242101012012-04-13 19:30:00.000
3243101012012-04-13 19:30:00.000
3244101012012-04-13 19:30:00.000
3245101012012-04-13 19:30:00.000
3246101012012-04-13 19:30:00.000
3247101012012-04-14 19:30:00.000
3248101012012-04-14 19:30:00.000
3249101012012-04-14 19:30:00.000
3250101012012-04-14 19:30:00.000
3251101012012-04-14 19:30:00.000
3004100872012-02-15 19:30:00.000
3005100872012-02-15 19:30:00.000
3006100872012-02-15 19:30:00.000
3007100872012-02-15 19:30:00.000
3008100872012-02-15 19:30:00.000
3009100872012-02-16 19:30:00.000
3010100872012-02-16 19:30:00.000
3011100872012-02-16 19:30:00.000
3012100872012-02-16 19:30:00.000
3013100872012-02-16 19:30:00.000
3014100872012-02-17 19:30:00.000
3015100872012-02-17 19:30:00.000
3016100872012-02-17 19:30:00.000
3017100872012-02-17 19:30:00.000
3018100872012-02-17 19:30:00.000
3019100872012-02-18 19:30:00.000
3020100872012-02-18 19:30:00.000
3021100872012-02-18 19:30:00.000
3022100872012-02-18 19:30:00.000
3023100872012-02-18 19:30:00.000
3024100872012-02-19 19:30:00.000
3025100872012-02-19 19:30:00.000
3026100872012-02-19 19:30:00.000
3027100872012-02-19 19:30:00.000
3028100872012-02-19 19:30:00.000
3371100532012-02-22 06:00:00.000
3372100532012-02-22 06:00:00.000
3373100532012-02-23 06:00:00.000
3374100532012-02-23 06:00:00.000
3375100532012-02-24 06:00:00.000
3376100532012-02-24 06:00:00.000
2090100472012-05-24 19:30:00.000
2091100472012-05-24 19:30:00.000
2092100472012-05-24 19:30:00.000
2093100472012-05-25 19:30:00.000
2094100472012-05-25 19:30:00.000
2095100472012-05-25 19:30:00.000
2096100472012-05-26 19:30:00.000
2097100472012-05-26 19:30:00.000
2098100472012-05-26 19:30:00.000
2099100472012-05-27 19:30:00.000
2100100472012-05-27 19:30:00.000
2101100472012-05-27 19:30:00.000
2102100472012-05-28 19:30:00.000
2103100472012-05-28 19:30:00.000
2118100472012-06-02 19:30:00.000
2151100472012-06-02 14:00:00.000
2152100472012-06-02 14:00:00.000
2153100472012-06-03 14:00:00.000
2154100472012-06-03 14:00:00.000
2155100472012-06-03 14:00:00.000
2156100472012-06-09 14:00:00.000
2157100472012-06-09 14:00:00.000
2158100472012-06-09 14:00:00.000
2022100462012-04-11 19:30:00.000
2023100462012-04-11 19:30:00.000
2024100462012-04-12 19:30:00.000
2025100462012-04-12 19:30:00.000
2087100462012-05-05 14:00:00.000
2088100462012-05-06 14:00:00.000
2089100462012-05-06 14:00:00.000
1990100452012-02-26 14:00:00.000
1991100452012-02-26 14:00:00.000
1992100452012-02-26 14:00:00.000
1993100452012-02-26 14:00:00.000
1994100452012-03-03 14:00:00.000
1995100452012-03-03 14:00:00.000
请问，如何能够：
1.过滤掉重复的pid
2.并留下时间最早的那个pid
谢谢
------解决方案--------------------
--id pid fullplaytime/*******select id,pid,fullplaytime from tbl_schedule where datediff(hour,getdate(),fullplaytime)>2 order by pid desc*******/select id,pid,fullplaytimefrom tbl_schedule twhere datediff(hour,getdate(),fullplaytime)>2 and not exists (select 1 from tbl_schedule where pid=t.pid and datediff(hour,getdate(),fullplaytime)>2 and fullplaytime<t.fullplaytime)order by pid desc
[解决办法]
select id,pid,fullplaytimefrom tbl_schedule twhere datediff(hour,getdate(),fullplaytime)>2 and not exists (select 1 from tbl_schedule where pid=t.pid and datediff(hour,getdate(),fullplaytime)>2 and (fullplaytime<t.fullplaytime OR fullplaytime=t.fullplaytime and id<t.id))order by pid desc
[解决办法]
别扭
select id,pid,fullplaytime from tbl_schedule Twhere fullplaytime > dateADD(hour,2,getdate())AND not exists (select 1 from tbl_schedule where pid=t.pid and fullplaytime > dateADD(hour,2,getdate()) and fullplaytime<t.fullplaytime)order by pid desc
