sql 查询问题 .求解决!!!
filesid filesname filesversion path filesUpLoadDate
6新建文本文档.txt1.0.0.0//update//新建文本文档.txt2012-11-15 16:24:30.820
7新建文本文档.txt1.0.0.1//update//新建文本文档.txt2012-11-15 16:31:16.377
8李剑锋.txt1.0.0.0//update//李剑锋.txt2012-11-15 16:42:55.757
9李剑锋.txt1.0.0.1//update//李剑锋.txt2012-11-15 16:42:58.480
10李剑锋.txt1.0.0.2//update//李剑锋.txt2012-11-15 17:48:55.663
....当然这里还有很多数据.
无论它有多少,我现在以文件名称 列(filesname 去掉相同的 ) 拿到
最新版本(filesversion )为最高的
以上数据 查出结果为:
7新建文本文档.txt1.0.0.1//update//新建文本文档.txt2012-11-15 16:31:16.377
10李剑锋.txt1.0.0.2//update//李剑锋.txt2012-11-15 17:48:55.663
请问sql 语句该怎么写 .
[最优解释]
select a.*
from tablename as a inner join (select filename,MAX(fileuploaddate) fileuploaddate from tablename group by filesname) as b
on a.filesname=b.filesname and a.fileuploaddate=b.fileuploaddate

[其他解释]

SELECT  *
 FROM    TB a
 WHERE   EXISTS ( SELECT 1
                  FROM   ( SELECT    filesname ,
                                     MAX(filesversion) filesversion
                           FROM      TB
                           GROUP BY  filesname
                         ) b
                  WHERE  a.filesname = b.filesname
                         AND a.filesversion = b.filesversion )

[其他解释]
select a.*
from tablename as a inner join (select filename,MAX(fileuploaddate) fileuploaddate from tablename group by filesname) as b
on a.filesname=b.filesname and a.fileuploaddate=b.fileuploaddate
理解万岁..........
[其他解释]
查询所有信息
[其他解释]

select filesid,filesname,filesversion,path,filesUpLoadDate from tb as a
where not exists(select 1 from tb as x where x.filesname=a.filesname and x.filesUpLoadDate>=a.filesUpLoadDate) 
 

[其他解释]

引用:

select a.*
from tablename as a inner join (select filename,MAX(fileuploaddate) fileuploaddate from tablename group by filesname) as b
on a.filesname=b.filesname and a.fileuploaddat……

谢了 ,他们以上的 可以实现 我目前问题能到的效果 .但是数据多了,就不行了. 你这个可以解决.
[其他解释]
通过开窗函数，对表字段tablename分一下区，再在区里通过时间倒序排序，取每个区里的排名第一的那个就Okl了
[其他解释]

SELECT *
FROM   (SELECT *,
               Row_number()OVER (partition BY filesname ORDER BY filesuploaddate DESC ) AS id
        FROM   table_a) a
WHERE  id <= 1