Sql Server - top 1 post per member ordered by createdDate

Question

I have a table Posts which has a memberID and createdDate.

I need to return the most recent post per member and the posts must be order with most recent at the top.

I am not sure how to do this with Sql Server, can anyone help?

Answer 1

select p.* 
 from post p 
 join (select memberId, 
              max(createdDate) as maxd 
        from post 
    group by memberId) as p2 on p.member_id=p2.member_id 
                            and p.createdDate=p2.maxd
 order by p.createdDate desc

Answer 2

WITH PostsRanked AS (
  SELECT
    memberID, postTitle, createdDate,
    RANK() OVER (
      PARTITION BY memberID
      ORDER BY createdDate DESC
    ) AS rk
  FROM Posts
)
  SELECT
    memberID, postTitle, createdDate
  FROM PostsRanked
  WHERE rk = 1
  ORDER BY createdDate DESC

Answer 3

Here is the working query

select p.* 
from post p join
(
    select memberId, max(createdDate) as maxd 
    from post 
    group by memberId

) as p2 on p.memberid = p2.memberid and p.createdDate=p2.maxd
order by p.createdDate desc