SQL 查询 - 组合 DISTINCT 和 TOP？

Question

我想触发以下查询:

SELECT DISTINCT TOP(5) fp.PostId FROM dbForumPosts fp
LEFT JOIN dbForumEntry fe ON fp.PostId = fe.PostId
Order by fe.Datemade DESC

但是，当我启动它时，我收到错误:

Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

我尝试更改查询，因此它使用 GROUP BY 代替，但随后出现以下问题:

Msg 8127, Level 16, State 1, Line 4
Column "dbForumEntry.Datemade" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

我想要什么:

将其视为一个论坛。有帖子 (dbForumPosts) 和条目 (dbForumEntry)。帖子中有 0 多个条目。

我想要的是获取具有最新事件的帖子(具有最新更新条目的帖子)。

Answer 1

您可以通过 row_number 找到每个 PostId 的最新 Datemade。然后您可以搜索最近的 5 篇帖子:

select  top 5 PostId
from    (
        select  PostId
        ,       Datemade
        ,       row_number() over (partition by PostId
                    order by Datemade) as rn
        from    dbForumEntry
        ) SubQueryAlias
where   rn = 1 -- Most recent row per PostId
order by
        Datemade desc

或者，您可以使用 group by 子查询实现相同的效果:

select  top 5 PostId
from    (
        select  PostId
        ,       max(Datemade) as LastDate
        from    dbForumEntry
        group by
                PostId
        ) SubQueryAlias
order by
        LastDate desc

如果dbForumEntry有一个ID列(例如ForumEntryId)，这样的查询可能会执行得更好。数据库可以运行此操作，而无需编译整个表的 row_number 或 max(Datemade)。

select  top 5 PostId
from    dbForumPosts fp
where   not exists -- No later entry for the same post exists
        (
        select  *
        from    dbForumPosts fp2
        where   fp2.PostId = fp.PostId
                and fp2.ForumEntryId > fp.ForumEntryId
        )
order by
        Datemade desc