[sql] 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.

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

I am pretty sure that it is:

SELECT last(column_name) FROM table

Becaause I use something similar:

SELECT last(id) FROM Status

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

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.

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)

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

Try this

SELECT id from comission_fees ORDER BY id DESC LIMIT 1

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

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

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; 

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

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

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)

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

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.

I am pretty sure that it is:

SELECT last(column_name) FROM table

Becaause I use something similar:

SELECT last(id) FROM Status

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)

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

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; 

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.

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.

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

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)

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

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

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

Try this

SELECT id from comission_fees ORDER BY id DESC LIMIT 1

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 )

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

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

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 )

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.

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

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