SQL Server: How to select multiple columns with 1 column being distinct?

Question

I am trying to do an SQL query on two tables to retrieve multiple columns with a certain column (PostID) to be distinct (and it is not the primary key of the that table).

In addition, I need the selected distinct rows to be the latest (one of the columns retrieved is the entry date).

Detailed description:

I am building a forum like application, using 3 tables to store data.

I use table1 to store user details, table2 to store the meta data for posts, table3 to store the post details, updates, and replies (postID is unique in table2 pointing towards an original post, while in table3, it is used to show the original post and updates and replies).

Table columns:

• table1 (UserID, FullName, mobile, etc.)
• table2 (postID, UserID, EntryDate, Deleted columns)
• table3 (postdetailsId, PostID, UserID, Entrydate, etc.)

I am trying to retrieve all the posts for 1 user in a gridview, my SQL query uses the USERID to retrieve all his posts from the table. However, it is retrieving the original post and all its updates, and I only want to retrieve the latest update of each post.

How can it be done fully in SQL (I know I can do it in C# with the returned results)?

My query:

SELECT T1.FullName, T3.PostID, T3.EntryDate, T3.Title
FROM   Table1 as T1, Table3 as T3
WHERE  T3.UserID = T1.UserID 
AND    T3.UserID = @UserID

Answer 1

You could use GROUP BY PostID along with MAX(EntryDate)

Answer 2

SELECT  *
FROM    (
        SELECT  posts.*, ROW_NUMBER() OVER (PARTITION BY post_updates.UserID, post_updates.PostID ORDER BY post_updates.EntryDate DESC) AS rn
        FROM    table1 users
        JOIN    table3 post_updates
        ON      post_updates.userID = users.UserID
        WHERE   users.UserID = @UserID
        ) q
WHERE   rn = 1