4

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?

Alex Aza
  • 76,499
  • 26
  • 155
  • 134
Matrix001
  • 1,272
  • 6
  • 30
  • 51
  • If you inserted the data into a table with a indentity column, you can use "SELECT @@IDENTITY FROM YourTable" to get the ID. Using the ID you can select the row you need. – Zachary Jun 12 '11 at 07:19

2 Answers2

9

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
Alex Aza
  • 76,499
  • 26
  • 155
  • 134
  • so top 1 would give me the last thread inserted..what if two users insert a thread near the same time... my field is datetime.. how accurate is it? – Matrix001 Jun 12 '11 at 07:21
  • 3
    Matrix001 - `ORDER BY` will order so the column with the largest datetime will be first. `TOP 1` will make sure that all other rows are ignored. Also, from my understanding it does not matter what other users do as you filter by concrete user `@UserID=t.UserId`, right? – Alex Aza Jun 12 '11 at 07:32
0

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
Romain Guidoux
  • 2,943
  • 4
  • 28
  • 48