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

User · Answer

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

User · Answer

If you re using MS SQL  you can try   SELECT TOP 1   FROM table Name ORDER BY unique column DESC

User · Answer

This is how you get the last record and update a field in Access DB   UPDATE  compalints  SET tkt   addzone  amp  -  amp  customer code  amp  -  amp  sn where sn in  select max sn  from compalints

User · Answer

select whatever columns you want from mytable  where mykey  select max mykey  from mytable

User · Answer

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

User · Answer

select whatever columns you want from mytable  where mykey  select max mykey  from mytable

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

SELECT   FROM TABLE WHERE ID    SELECT MAX ID  FROM TABLE

User · Answer

If you re using MS SQL  you can try   SELECT TOP 1   FROM table Name ORDER BY unique column DESC

User · Answer

I am pretty sure that it is   SELECT last column name  FROM table   Becaause I use something similar   SELECT last id  FROM Status

User · Answer

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

User · Answer

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

User · Answer

I am pretty sure that it is   SELECT last column name  FROM table   Becaause I use something similar   SELECT last id  FROM Status

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

SELECT   from Employees where  Employee ID    ALL  SELECT MAX  Employee ID   from Employees

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

select whatever columns you want from mytable  where mykey  select max mykey  from mytable

User · Answer

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

User · Answer

select whatever columns you want from mytable  where mykey  select max mykey  from mytable

User · Answer

SELECT   from Employees where  Employee ID    ALL  SELECT MAX  Employee ID   from Employees

User · Answer

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

User · Answer

SELECT   FROM TABLE WHERE ID    SELECT MAX ID  FROM TABLE

User · Answer

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

User · Answer

This is how you get the last record and update a field in Access DB   UPDATE  compalints  SET tkt   addzone  amp  -  amp  customer code  amp  -  amp  sn where sn in  select max sn  from compalints

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Try this  SELECT id from comission fees ORDER BY id DESC LIMIT 1

User · Answer

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

User · Answer

If you re using MS SQL  you can try   SELECT TOP 1   FROM table Name ORDER BY unique column DESC

User · Answer

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

User · Answer

If you re using MS SQL  you can try   SELECT TOP 1   FROM table Name ORDER BY unique column DESC

User · Answer

Try this  SELECT id from comission fees ORDER BY id DESC LIMIT 1

User · Answer

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

[sql] How to read the last row with SQL Server

Examples related to sql

Examples related to sql-server