Geek Answers Handbook

SQL Server - how to choose the most recent record for each user?

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

Basically, when a user submits the last thread (for example, in this forum), he redirects the user to a page with the heading of the thread, the paragraph of the thread, and data such as the username and time when the thread was sent.

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 I am facing is that I need to add some instruction or aggregation function in order to return to me the last row that was entered into the thread table. How can I do it? what do i need to add?

+4
source share
2 answers

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

 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
+9
source

I'm not sure I understood your question exactly, 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
0
source

More articles:


All Articles