How to read the last row with SQL Server

Question

What is the most efficient way to read the last row with SQL Server?

The table is indexed on a unique key -- the "bottom" key values represent the last row.

Answer 1

If you're using MS SQL, you can try:

SELECT TOP 1 * FROM table_Name ORDER BY unique_column DESC 

Answer 2

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

Answer 3

You'll need some sort of uniquely identifying column in your table, like an auto-filling primary key or a datetime column (preferably the primary key). Then you can do this:

SELECT * FROM table_name ORDER BY unique_column DESC LIMIT 1

The ORDER BY column tells it to rearange the results according to that column's data, and the DESC tells it to reverse the results (thus putting the last one first). After that, the LIMIT 1 tells it to only pass back one row.

Answer 4

If some of your id are in order, i am assuming there will be some order in your db

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

Answer 5

I think below query will work for SQL Server with maximum performance without any sortable column

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

Hope you have understood it... :)

Answer 6

SELECT TOP 1 id from comission_fees ORDER BY id DESC 

Answer 7

I tried using last in sql query in SQl server 2008 but it gives this err: " 'last' is not a recognized built-in function name."

So I ended up using :

select max(WorkflowStateStatusId) from WorkflowStateStatus 

to get the Id of the last row. One could also use

Declare @i int
set @i=1
select WorkflowStateStatusId from Workflow.WorkflowStateStatus
 where WorkflowStateStatusId not in (select top (
   (select count(*) from Workflow.WorkflowStateStatus) - @i ) WorkflowStateStatusId from .WorkflowStateStatus)

Answer 8

You can use last_value: SELECT LAST_VALUE(column) OVER (PARTITION BY column ORDER BY column)...

I test it at one of my databases and it worked as expected.

You can also check de documentation here: https://msdn.microsoft.com/en-us/library/hh231517.aspx

Answer 9

OFFSET and FETCH NEXT are a feature of SQL Server 2012 to achieve SQL paging while displaying results.

The OFFSET argument is used to decide the starting row to return rows from a result and FETCH argument is used to return a set of number of rows.

SELECT *
FROM table_name
ORDER BY unique_column desc
OFFSET 0 Row
FETCH NEXT 1 ROW ONLY

Answer 10

In order to retrieve the last row of a table for MS SQL database 2005, You can use the following query:

select top 1 column_name from table_name order by column_name desc; 

Note: To get the first row of the table for MS SQL database 2005, You can use the following query:

select top 1 column_name from table_name; 

Answer 11

If you don't have any ordered column, you can use the physical id of each lines:

SELECT top 1 sys.fn_PhysLocFormatter(%%physloc%%) AS [File:Page:Slot], 
              T.*
FROM MyTable As T
order by sys.fn_PhysLocFormatter(%%physloc%%) DESC

Answer 12

This is how you get the last record and update a field in Access DB.

UPDATE compalints SET tkt = addzone &'-'& customer_code &'-'& sn where sn in (select max(sn) from compalints )

Answer 13

If you have a Replicated table, you can have an Identity=1000 in localDatabase and Identity=2000 in the clientDatabase, so if you catch the last ID you may find always the last from client, not the last from the current connected database. So the best method which returns the last connected database is:

SELECT IDENT_CURRENT('tablename')

Answer 14

Well I'm not getting the "last value" in a table, I'm getting the Last value per financial instrument. It's not the same but I guess it is relevant for some that are looking to look up on "how it is done now". I also used RowNumber() and CTE's and before that to simply take 1 and order by [column] desc. however we nolonger need to...

I am using SQL server 2017, we are recording all ticks on all exchanges globally, we have ~12 billion ticks a day, we store each Bid, ask, and trade including the volumes and the attributes of a tick (bid, ask, trade) of any of the given exchanges.

We have 253 types of ticks data for any given contract (mostly statistics) in that table, the last traded price is tick type=4 so, when we need to get the "last" of Price we use :

select distinct T.contractId,
LAST_VALUE(t.Price)over(partition by t.ContractId order by created ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
from [dbo].[Tick] as T
where T.TickType=4

You can see the execution plan on my dev system it executes quite efficient, executes in 4 sec while the exchange import ETL is pumping data into the table, there will be some locking slowing me down... that's just how live systems work.

Answer 15

SELECT * from Employees where [Employee ID] = ALL (SELECT MAX([Employee ID]) from Employees)

Answer 16

It is very simple

select top 10 * from TableName order by 1 desc

Answer 17

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

Answer 18

I am pretty sure that it is:

SELECT last(column_name) FROM table

Becaause I use something similar:

SELECT last(id) FROM Status