Reputation: 17673

How to read the last row with SQL Server

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.

Upvotes: 111

Answers (18)

manas

Reputation: 11

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

Upvotes: 0

lionelmessi

Reputation: 1144

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)

Upvotes: 13

Muhammad Sajid

Reputation: 9

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
        )

Upvotes: 0

Ashish Pathak

Reputation: 824

SELECT TOP 1 id from comission_fees ORDER BY id DESC

Upvotes: 4

Vintage Coder

Reputation: 501

It is very simple

select top 10 * from TableName order by 1 desc

Upvotes: 0

Jignesh Bhayani

Reputation: 110

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

Upvotes: 2

Walter Verhoeven

Reputation: 4411

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.

Upvotes: 0

Rogerio Gelonezi

Reputation: 334

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')

Upvotes: 0

Gil Gomes

Reputation: 96

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

Upvotes: 4

ConsiderItDone

Reputation: 166

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... :)

Upvotes: 5

Sylvain Rodrigue

Reputation: 4963

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

Upvotes: 1

Neha Verma

Reputation: 29

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)

Upvotes: 3

Hamza Abuzahra

Reputation: 1

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

Upvotes: -1

Deepali Shahabadi

Reputation:

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;

Upvotes: 2

maniakk

Reputation: 1

I am pretty sure that it is:

SELECT last(column_name) FROM table

Becaause I use something similar:

SELECT last(id) FROM Status

Upvotes: -6

willurd

Reputation: 12023

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.

Upvotes: 18

EggyBach

Reputation: 4228

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

SELECT TOP 1 * FROM table_Name ORDER BY unique_column DESC

Upvotes: 210

Adam Pierce

Reputation: 34345

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

Upvotes: 23

How to read the last row with SQL Server

Answers (18)

Related Questions