Clever Geek Handbook

SQL Server: how to select multiple columns with one column?

I am trying to execute a SQL query on two tables to get multiple columns with a specific column (PostID) to be different (and this is not the primary key of that table).

Also, I need the selected individual rows to be the last one (one of the columns found is the post date).

Detailed description:

I am creating a forum as an application using 3 tables to store data.

I use table1 to store user data, table2 to store metadata for posts, table3 to store information about posts, updates and replies (postID is unique in table2 pointing to the original column, and in table 3 it is used to show the original post, 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 get all posts for 1 user in a gridview, my SQL query is using USERID to retrieve all his posts from the table. However, it fetches the original post and all its updates, and I only want to get the latest update of each post.

How can this be done entirely in SQL (I know I can do this in C # with results returned)?

My request:

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
+2


source to share


2 answers


You can use GROUP BY PostID

together withMAX(EntryDate)



+2


source 


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


+1


source






More articles:


All Articles