How to read the last row with SQL Server

SqlSql Server

Sql Problem Overview


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.

Sql Solutions


Solution 1 - Sql

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

SELECT TOP 1 * FROM table_Name ORDER BY unique_column DESC 

Solution 2 - Sql

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

Solution 3 - Sql

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.

Solution 4 - Sql

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)

Solution 5 - Sql

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

Solution 6 - Sql

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)

Solution 7 - Sql

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

Solution 8 - Sql

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

Solution 9 - Sql

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; 

Solution 10 - Sql

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

Solution 11 - Sql

Try this

SELECT id from comission_fees ORDER BY id DESC LIMIT 1

Solution 12 - Sql

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

Solution 13 - Sql

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 )

Solution 14 - Sql

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

Solution 15 - Sql

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. execution plan against 85,697,659 rows

Solution 16 - Sql

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

Solution 17 - Sql

I am pretty sure that it is:

SELECT last(column_name) FROM table

Becaause I use something similar:

SELECT last(id) FROM Status

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
Questionrp.View Question on Stackoverflow
Solution 1 - SqlEggyBachView Answer on Stackoverflow
Solution 2 - SqlAdam PierceView Answer on Stackoverflow
Solution 3 - SqlwillurdView Answer on Stackoverflow
Solution 4 - SqllionelmessiView Answer on Stackoverflow
Solution 5 - SqlConsiderItDoneView Answer on Stackoverflow
Solution 6 - SqlNeha VermaView Answer on Stackoverflow
Solution 7 - SqlGil GomesView Answer on Stackoverflow
Solution 8 - SqlJignesh BhayaniView Answer on Stackoverflow
Solution 9 - SqlDeepali ShahabadiView Answer on Stackoverflow
Solution 10 - SqlSylvain RodrigueView Answer on Stackoverflow
Solution 11 - SqlAshish PathakView Answer on Stackoverflow
Solution 12 - SqlmanasView Answer on Stackoverflow
Solution 13 - SqlMuhammad SajidView Answer on Stackoverflow
Solution 14 - SqlRogerio GeloneziView Answer on Stackoverflow
Solution 15 - SqlWalter VerhoevenView Answer on Stackoverflow
Solution 16 - SqlHamza AbuzahraView Answer on Stackoverflow
Solution 17 - SqlmaniakkView Answer on Stackoverflow