Select the last row in a SQL table

Question

Is it possible to return the last row of a table in MS SQL Server. I am using an auto increment field for the ID and i want to get the last one just added to join it with something else. Any idea?

Here's the code:

const string QUERY = @"INSERT INTO Questions (ID, Question, Answer, CategoryID, Permission) " 
                   + @"VALUES (@ID, @Question, @Answer, @CategoryID, @Permission) "; 

using (var cmd = new SqlCommand(QUERY, conn)) 
{ 
    cmd.Parameters.AddWithValue("@Question", question); 
    cmd.Parameters.AddWithValue("@Answer", answer); 
    cmd.Parameters.AddWithValue("@CategoryID", lastEdited);
    cmd.Parameters.AddWithValue("@Permission", categoryID);
    cmd.ExecuteNonQuery(); 
}

Answer 1

select top 1 * from yourtable order by id desc

Answer 2

use

• scope_identity() returns the last identity value generated in this session and this scope

• ident_current() returns the last identity value generated for a particular table in any session and any scope

  select ident_current( 'yourTableName' )

will return the last identity created by a different session.

Most of the time you should use scope_identity() right after an insert statement like so.

--insert statement
SET @id = CAST(SCOPE_IDENTITY() AS INT)

• MSDN Link - Scope_Identity()
• MSDN Link - Ident_Current

Answer 3

Not safe - could have multiple inserts going on at the same time and the last row you'd get might not be yours. You're better off using SCOPE_IDENTITY() to get the last key assigned for your transaction.

Answer 4

With a simple select you can do something like this:

SELECT *
FROM table_name
WHERE IDColumn=(SELECT max(IDColum) FROM table_name)

Answer 5

I'm not sure of your version of SQL Server, but look for the OUTPUT clause of ther INSERT statement. You can capture a set of rows with this clause

Answer 6

Since the questioner is using .NET, here's a modified example of how to do it. (I removed ID from the insert list since it's autoincrement--the original example would fail. I also assume ID is an SQL int, not a bigint.)

        const string QUERY = @"INSERT INTO Questions (Question, Answer, CategoryID, Permission) "
                           + @"VALUES (@Question, @Answer, @CategoryID, @Permission);"
                           + @"SELECT @ID = SCOPE_IDENTITY();";

        using (var cmd = new SqlCommand(QUERY, conn)) 
        { 
            cmd.Parameters.AddWithValue("@Question", question); 
            cmd.Parameters.AddWithValue("@Answer", answer); 
            cmd.Parameters.AddWithValue("@CategoryID", lastEdited);
            cmd.Parameters.AddWithValue("@Permission", categoryID);
            cmd.Parameters.Add("@ID", System.Data.SqlDbType.Int).Direction = ParameterDirection.Output;
            cmd.ExecuteNonQuery();

            int id = (int)cmd.Parameters["@ID"].Value;
        }

EDITED: I also suggest considering LINQ to SQL instead of hand-coding SqlCommand objects--it's much better (faster to code, easier to use) for many common scenarios.