筛选重复字段的记录，求助？急，急，急！！！

[kevin_hekai]

id username fileclass uploadtime
1 yy 6 2004-11-30 11:27:08.000
2 rr 6 2004-11-30 11:24:08.000
3 ff 6 2004-11-30 11:07:31.000
4 ff 5 2004-11-30 10:58:19.000
5 gg 5 2004-11-30 10:17:59.000
6 hh 4 2004-11-30 10:16:37.000
7 ee 6 2004-11-29 16:16:38.000
8 ww 6 2004-11-29 16:10:38.000
9 gg 3 2004-11-27 14:56:28.000
10 uu 3 2004-11-27 13:49:38.000
...

现在要筛选出username字段不重复而且fileclass=6的纪录，我写了一个但不成功，在线求助！！

SELECT top 10* FROM files_log_sql WHERE username IN (SELECT distinct username FROM files_log_sql where fileclass=6)order by uploadtime DESC

[hbzh1014]

不知道你有没有什么特别的要求，或许可以解决问题。
select a.Id,a.userName,a.fileclass,b.uploadtime from (select min(id) id,username,fileclass from files_log_sql where fileclass = '6' group by username,fileclass) a
left outer join files_log_sql b on a.id = b.id
希望有些启发！

[esterlee]

select * from files_log_sql where fileclass=6 group by username

[rustical]

第一种办法:保留 大的 id
select * from files_log_sql where id in
( select max(id) as id from files_log_sql where fileclass='6' group by username)
and fileclass ='6'

第二种办法:保留 小的 id
select * from files_log_sql where id in
( select min(id) as id from files_log_sql where fileclass='6' group by username)
and fileclass ='6'

[rustical]

第一种办法:保留 大的 id
select * from files_log_sql where id in
( select max(id) as id from files_log_sql where fileclass='6' group by username)

第二种办法:保留 小的 id
select * from files_log_sql where id in
( select min(id) as id from files_log_sql where fileclass='6' group by username)

[luobinsen007]

SELECT DISTINCT username FROM files_log_sql
where fileclass = 6

[lienzhu]

select * from files_log_sql where fileclass=6 and username in (select username from files_log_sql group by username having count(*)=1)

[fenlin]

SELECT top 10 * FROM files_log_sql WHERE username IN(SELECT distinct username FROM files_log_sql) and fileclass=6 order by uploadtime DESC

[lienzhu]

分析你的sql要求

select top 10 * from files_log_sql where fileclass=6 and username in (select username from files_log_sql group by username having count(*)=1) order by uploadtime DESC