Coder Perfect

With SQL Server, how do you read the last row?

Problem

What is the most efficient SQL Server method for reading the last row?

The table is indexed using a single key, with the “bottom” key values corresponding to the last row.

Asked by rp.

Solution #1

If you’re using MS SQL, try the following:

SELECT TOP 1 * FROM table_Name ORDER BY unique_column DESC 

Answered by EggyBach

Solution #2

select whatever,columns,you,want from mytable
 where mykey=(select max(mykey) from mytable);

Answered by Adam Pierce

Solution #3

In your table, you’ll need a unique identifying column, such as an auto-filling primary key or a datetime column (preferably the primary key). Then you can perform the following:

SELECT * FROM table_name ORDER BY unique_column DESC LIMIT 1

The ORDER BY column instructs it to rearrange the results based on the data in that column, while the DESC instructs it to reverse the results (thus putting the last one first). The LIMIT 1 tells it to only return one row after that.

Answered by willurd

Solution #4

If some of your ids are in order, I’m guessing your database is as well.

SELECT * FROM TABLE WHERE ID = (SELECT MAX(ID) FROM TABLE)

Answered by lionelmessi

Solution #5

Without any sortable columns, I believe the query below will execute optimally in SQL Server.

SELECT * FROM table 
WHERE ID not in (SELECT TOP (SELECT COUNT(1)-1 
                             FROM table) 
                        ID 
                 FROM table)

I hope you’ve grasped what I’m saying…:)

Answered by ConsiderItDone

Post is based on https://stackoverflow.com/questions/177323/how-to-read-the-last-row-with-sql-server