查询以返回每个不同列值的头项 [英] Query to Return Top Items for Each Distinct Column Value

问题描述

如果我有一个包含以下字段的表

If I have a table with the following fields

ID, SomeFK, SomeTime

我将如何编写查询以返回每个SomeFK的最新/前三项(基于SomeTime).

How would I write a query return the latest/top 3 items (based on SomeTime) for each SomeFK.

因此，结果可能看起来像

So, the result might look like

SomeFK    Sometime
0         2012-07-05 
0         2012-07-04 
0         2012-07-03 
1         2012-07-03 
1         2012-07-02 
1         2012-07-01 
2         2012-07-03 
2         2012-07-02 
2         2012-07-01 
....etc....

为特定的SomeFK返回最新的项目很容易，但是我只是想不出上面的方法.我也觉得这应该死得很简单！

Returning the latest items for a particular SomeFK is easy, but i just can't think how to do it for the above. I also feel it should be dead simple!

抱歉，我错过了关键信息.这是针对SQL2000的，因此不能使用ROW_NUMBER()！

Apologies, I missed a key bit of information. this is for SQL2000, so ROW_NUMBER() can't be used!

推荐答案

SELECT SomeFk, SomeTime 
FROM 
    (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY SomeFK ORDER BY sometime desc) rn
    FROM yourtable
    ) v
WHERE rn<=3
ORDER BY somefk, rn

对于SQL 2000，我建议升级到受支持的平台.

For SQL 2000, I recommend upgrading to a supported platform.

但是，如果您坚持.

select *
from yourtable t1
where
    (select COUNT(*)
     from yourtable
     where somefk = t1.somefk
     and sometime>=t1.sometime
    ) <=3

这篇关于查询以返回每个不同列值的头项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！