Geek asks and answers

Maximize group usage

I have a table column and postcontent.

Each time a post is edited, a record is added to the postcontent table.

At each such moment, the submitted name version will increase by 1 in the postcontent table.

How can I get PostId starting with post and [Description] from the postcontent table Where is the maximum version for each message group by message id i.e. I expect one raw for each message, so this is the "maximum version" for the message

+3
source share
3 answers
SELECT 
    p.[PostID],
    pc.[Description]
FROM
    [Post] AS p
INNER JOIN
    [PostContent] AS pc
    on p.PostID = pc.PostID 
WHERE
    pc.[Version] = (SELECT MAX([Version]) FROM PostContent WHERE PostID = p.PostID)
+1
source 
select P.PostID,
       PC.[Description]
from Post as P
inner join 
  (
    select PostID,
           [Description],
           row_number() over(partition by PostID order by [Version] desc) as rn
    from PostContent
  ) as PC
  on P.PostID = PC.PostID
where PC.rn = 1
+2
source

I think you have a table PostIdin PostContentas a foreign key, and you want to get everything PostIdwith their version of Heighhest.

select max(version),PostId from PostContent groupby PostId
+1
source

More articles:


All Articles