SQL Server – How to select the most recent record per user?

I am trying to execute a SQL command which should draw on the last row that entered the database.

Basically, when a user submits the last thread (like in this forum), it redirects the user to a page with his thread title, thread paragraph and data such as a user name and time the thread was submitted.

I started working on the statement:

SELECT @UserID, u.UsersName, t.ThreadTitle, t.ThreadParagraph
FROM Users as u
INNER JOIN Threads as t ON u.UserID = t.UserID
Where @UserID = t.UserId

The problem that I have is that i need to add some statement or aggregate function to return me the last row that was entered in the threads table. How do I do this? what do I need to add?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

In MS SQL you can use TOP 1 for this, you also need to order by your created date time column descending.

SELECT TOP 1 @UserID, u.UsersName, t.ThreadTitle, t.ThreadParagraph
FROM Users as u
    INNER JOIN Threads as t ON u.UserID = t.UserID
Where @UserID=t.UserId
ORDER BY [YourDateTimeFiled] DESC

Method 2

I’m not sure I have exactly understood your question, but if you have a thread id (primary key in your threads table), try this

SELECT @UserID, u.UsersName, t.ThreadTitle, t.ThreadParagraph
FROM Users as u
INNER JOIN Threads as t ON u.UserID = t.UserID
Where @UserID=t.UserId
ORDER BY t.id DESC
LIMIT 0, 1

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0