SQL Server - How to select the most recent record

问题:

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?

回答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

回答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