SQL查询-结合DISTINCT和TOP? [英] SQL Query - Combine DISTINCT and TOP?

问题描述

我有以下要触发的查询:

I have the following query I want to fire:

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

但是，当我触发它时，我得到了错误:

However, when I fire it, I get the error:

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

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

I tried to change the query, so it used GROUP BY instead, but then I have the following problem:

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条条目.

Think of this as a forum. There are posts (dbForumPosts) and entries (dbForumEntry). There are 0-many entries pr post.

我想要的是获取具有最新活动的帖子(具有最新更新条目的帖子).

What I want is to the get posts with the most recent activity (posts with the latest updated entries in).

推荐答案

您可以在每个PostId和row_number中找到最新的Datemade.然后，您可以搜索最新的5篇帖子:

You could find the most recent Datemade per PostId with row_number. Then you can search for the most recent 5 posts:

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子查询来实现相同目的:

Alternatively, you can achieve the same with a group by subquery:

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).

If dbForumEntry has an ID column (say ForumEntryId), a query like this might perform better. The database can run this without compiling the row_number or max(Datemade) for the entire table.

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

这篇关于SQL查询-结合DISTINCT和TOP?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！