How do I select last 5 rows in a table without sorting

Question

I want to select the last 5 records from a table in SQL Server without arranging the table in ascending or descending order

User · Answer

There is a handy trick that works in some databases for ordering in database order,

SELECT * FROM TableName ORDER BY true

Apparently, this can work in conjunction with any of the other suggestions posted here to leave the results in "order they came out of the database" order, which in some databases, is the order they were last modified in.

User · Answer

When number of rows in table is less than 5 the answers of Matt Hamilton and msuvajac  is Incorrect  Because a TOP N rowcount value may not be negative   A great example can be found Here

User · Answer

The way your question is phrased makes it sound like you think you have to physically resort the data in the table in order to get it back in the order you want  If so  this is not the case  the ORDER BY clause exists for this purpose  The physical order in which the records are stored remains unchanged when using ORDER BY  The records are sorted in memory  or in temporary disk space  before they are returned   Note that the order that records get returned is not guaranteed without using an ORDER BY clause  So  while any of the the suggestions here may work  there is no reason to think they will continue to work  nor can you prove that they work in all cases with your current database  This is by design - I am assuming it is to give the database engine the freedom do as it will with the records in order to obtain best performance in the case where there is no explicit order specified   Assuming you wanted the last 5 records sorted by the field Name in ascending order  you could do something like this  which should work in either SQL 2000 or 2005   select Name  from       select top 5 Name      from MyTable      order by Name desc   a  order by Name asc

User · Answer

You need to count number of rows inside table   say we have 12 rows   then subtract 5 rows from them   we are now in 7   select   where index column   7   select   from users where user id  gt      select COUNT    from users  - 5    you can order them ASC or DESC  But when using this code  select TOP 5 from users order by user id DESC   it will not be ordered easily

User · Answer

The way your question is phrased makes it sound like you think you have to physically resort the data in the table in order to get it back in the order you want  If so  this is not the case  the ORDER BY clause exists for this purpose  The physical order in which the records are stored remains unchanged when using ORDER BY  The records are sorted in memory  or in temporary disk space  before they are returned   Note that the order that records get returned is not guaranteed without using an ORDER BY clause  So  while any of the the suggestions here may work  there is no reason to think they will continue to work  nor can you prove that they work in all cases with your current database  This is by design - I am assuming it is to give the database engine the freedom do as it will with the records in order to obtain best performance in the case where there is no explicit order specified   Assuming you wanted the last 5 records sorted by the field Name in ascending order  you could do something like this  which should work in either SQL 2000 or 2005   select Name  from       select top 5 Name      from MyTable      order by Name desc   a  order by Name asc

User · Answer

Try this  if you don t have a primary key or identical column   select  Stu Id   Student Name    City    Registered           RowNum   row number   OVER  ORDER BY  SELECT 0       from student ORDER BY RowNum desc

User · Answer

Well  the  last five rows  are actually the last five rows depending on your clustered index   Your clustered index  by definition  is the way that he rows are ordered  So you really can t get the  last five rows  without some order   You can  however  get the last five rows as it pertains to the clustered index   SELECT TOP 5   FROM MyTable ORDER BY MyCLusteredIndexColumn1  MyCLusteredIndexColumnq       MyCLusteredIndexColumnN DESC

User · Answer

Get the count of that table   select count    from TABLE select top count   from TABLE where  primary key row  NOT IN  select top  count-5   primary key row  from TABLE

User · Answer

Without an order  this is impossible   What defines the  bottom    The following will select 5 rows according to how they are stored in the database   SELECT TOP 5   FROM  TableName

User · Answer

Well  the  last five rows  are actually the last five rows depending on your clustered index   Your clustered index  by definition  is the way that he rows are ordered  So you really can t get the  last five rows  without some order   You can  however  get the last five rows as it pertains to the clustered index   SELECT TOP 5   FROM MyTable ORDER BY MyCLusteredIndexColumn1  MyCLusteredIndexColumnq       MyCLusteredIndexColumnN DESC

User · Answer

Search 5 records from last records you can use this   SELECT   FROM   Table Name WHERE  ID  lt   IDENT CURRENT  Table Name   AND ID  gt   IDENT CURRENT  Table Name   - 5

User · Answer

If you know how many rows there will be in total you can use the ROW NUMBER   function  Here s an examble from MSDN  http   msdn microsoft com en-us library ms186734 aspx   USE AdventureWorks  GO WITH OrderedOrders AS       SELECT SalesOrderID  OrderDate      ROW NUMBER   OVER  ORDER BY OrderDate  AS  RowNumber      FROM Sales SalesOrderHeader     SELECT    FROM OrderedOrders  WHERE RowNumber BETWEEN 50 AND 60

User · Answer

In SQL Server  it does not seem possible without using ordering in the query  This is what I have used   SELECT   FROM       SELECT TOP 5       FROM  MyTable      ORDER BY Id DESC   Primary Key     AS T ORDER BY T Id ASC    Primary Key

User · Answer

Thanks to  Apps Tawale   Based on his answer  here s a bit of another  my  version    To select last 5 records without an identity column    select top 5        RowNum   row number   OVER  ORDER BY  SELECT 0       from   dbo   ViewEmployeeMaster  ORDER BY RowNum desc   Nevertheless  it has an order by  but on RowNum     Note 1   The above query will reverse the order of what we get when we run the main select  query   So to maintain the order  we can slightly go like   select    RowNum2   row number   OVER  ORDER BY  SELECT 0       from            select top 5    RowNum   row number   OVER  ORDER BY  SELECT 0               from   dbo   ViewEmployeeMaster          ORDER BY RowNum desc        as t1 order by RowNum2 desc   Note 2   Without an identity column  the query takes a bit of time in case of large data

User · Answer

If you know how many rows there will be in total you can use the ROW NUMBER   function  Here s an examble from MSDN  http   msdn microsoft com en-us library ms186734 aspx   USE AdventureWorks  GO WITH OrderedOrders AS       SELECT SalesOrderID  OrderDate      ROW NUMBER   OVER  ORDER BY OrderDate  AS  RowNumber      FROM Sales SalesOrderHeader     SELECT    FROM OrderedOrders  WHERE RowNumber BETWEEN 50 AND 60

User · Answer

Last 5 rows retrieve in mysql    This query working perfectly  SELECT   FROM  SELECT   FROM recharge ORDER BY sno DESC LIMIT 5 sub ORDER BY sno ASC   or  select sno from select sno from recharge order by sno desc limit 5  as t where t sno order by t sno asc

User · Answer

DECLARE  MYVAR  NVARCHAR 100  DECLARE  step  int SET  step   0    DECLARE MYTESTCURSOR CURSOR DYNAMIC  FOR SELECT col FROM  dbo   table  OPEN MYTESTCURSOR FETCH LAST FROM MYTESTCURSOR INTO  MYVAR print  MYVAR    WHILE  step  lt  10 BEGIN        FETCH PRIOR FROM MYTESTCURSOR INTO  MYVAR         print  MYVAR          SET  step    step   1  END    CLOSE MYTESTCURSOR DEALLOCATE MYTESTCURSOR

User · Answer

Without an order  this is impossible   What defines the  bottom    The following will select 5 rows according to how they are stored in the database   SELECT TOP 5   FROM  TableName

User · Answer

Last 5 rows retrieve in mysql    This query working perfectly  SELECT   FROM  SELECT   FROM recharge ORDER BY sno DESC LIMIT 5 sub ORDER BY sno ASC   or  select sno from select sno from recharge order by sno desc limit 5  as t where t sno order by t sno asc

User · Answer

Try this  if you don t have a primary key or identical column   select  Stu Id   Student Name    City    Registered           RowNum   row number   OVER  ORDER BY  SELECT 0       from student ORDER BY RowNum desc

User · Answer

The way your question is phrased makes it sound like you think you have to physically resort the data in the table in order to get it back in the order you want  If so  this is not the case  the ORDER BY clause exists for this purpose  The physical order in which the records are stored remains unchanged when using ORDER BY  The records are sorted in memory  or in temporary disk space  before they are returned   Note that the order that records get returned is not guaranteed without using an ORDER BY clause  So  while any of the the suggestions here may work  there is no reason to think they will continue to work  nor can you prove that they work in all cases with your current database  This is by design - I am assuming it is to give the database engine the freedom do as it will with the records in order to obtain best performance in the case where there is no explicit order specified   Assuming you wanted the last 5 records sorted by the field Name in ascending order  you could do something like this  which should work in either SQL 2000 or 2005   select Name  from       select top 5 Name      from MyTable      order by Name desc   a  order by Name asc

User · Answer

select   from table limit 5 offset  select count    from table  - 5

User · Answer

select    from table  order by empno primary key  desc  fetch first 5 rows only

User · Answer

You can retrieve them from memory  So first you get the rows in a DataSet  and then get the last 5 out of the DataSet

User · Answer

If you know how many rows there will be in total you can use the ROW NUMBER   function  Here s an examble from MSDN  http   msdn microsoft com en-us library ms186734 aspx   USE AdventureWorks  GO WITH OrderedOrders AS       SELECT SalesOrderID  OrderDate      ROW NUMBER   OVER  ORDER BY OrderDate  AS  RowNumber      FROM Sales SalesOrderHeader     SELECT    FROM OrderedOrders  WHERE RowNumber BETWEEN 50 AND 60

User · Answer

Thanks to  Apps Tawale   Based on his answer  here s a bit of another  my  version    To select last 5 records without an identity column    select top 5        RowNum   row number   OVER  ORDER BY  SELECT 0       from   dbo   ViewEmployeeMaster  ORDER BY RowNum desc   Nevertheless  it has an order by  but on RowNum     Note 1   The above query will reverse the order of what we get when we run the main select  query   So to maintain the order  we can slightly go like   select    RowNum2   row number   OVER  ORDER BY  SELECT 0       from            select top 5    RowNum   row number   OVER  ORDER BY  SELECT 0               from   dbo   ViewEmployeeMaster          ORDER BY RowNum desc        as t1 order by RowNum2 desc   Note 2   Without an identity column  the query takes a bit of time in case of large data

User · Answer

Well  the  last five rows  are actually the last five rows depending on your clustered index   Your clustered index  by definition  is the way that he rows are ordered  So you really can t get the  last five rows  without some order   You can  however  get the last five rows as it pertains to the clustered index   SELECT TOP 5   FROM MyTable ORDER BY MyCLusteredIndexColumn1  MyCLusteredIndexColumnq       MyCLusteredIndexColumnN DESC

User · Answer

select    from table  order by empno primary key  desc  fetch first 5 rows only

User · Answer

Suppose you have an index on id  this will be lightning fast   SELECT   FROM  MyTable  WHERE  id   gt   SELECT MAX  id   - 5 FROM  MyTable

User · Answer

This is just about the most bizarre query I ve ever written  but I m pretty sure it gets the  last 5  rows from a table without ordering   select    from issues where issueid not in       select top            select count    from issues  - 5       issueid     from issues     Note that this makes use of SQL Server 2005 s ability to pass a value into the  top  clause - it doesn t work on SQL Server 2000

User · Answer

Get the count of that table   select count    from TABLE select top count   from TABLE where  primary key row  NOT IN  select top  count-5   primary key row  from TABLE

User · Answer

This is just about the most bizarre query I ve ever written  but I m pretty sure it gets the  last 5  rows from a table without ordering   select    from issues where issueid not in       select top            select count    from issues  - 5       issueid     from issues     Note that this makes use of SQL Server 2005 s ability to pass a value into the  top  clause - it doesn t work on SQL Server 2000

User · Answer

In SQL Server  it does not seem possible without using ordering in the query  This is what I have used   SELECT   FROM       SELECT TOP 5       FROM  MyTable      ORDER BY Id DESC   Primary Key     AS T ORDER BY T Id ASC    Primary Key

User · Answer

Without an order  this is impossible   What defines the  bottom    The following will select 5 rows according to how they are stored in the database   SELECT TOP 5   FROM  TableName

User · Answer

You need to count number of rows inside table   say we have 12 rows   then subtract 5 rows from them   we are now in 7   select   where index column   7   select   from users where user id  gt      select COUNT    from users  - 5    you can order them ASC or DESC  But when using this code  select TOP 5 from users order by user id DESC   it will not be ordered easily

User · Answer

If you do not want to arrange the table in ascending or descending order  Use this   select   from table limit 5 offset  select count    from table  - 5

User · Answer

There is a handy trick that works in some databases for ordering in database order,

SELECT * FROM TableName ORDER BY true

Apparently, this can work in conjunction with any of the other suggestions posted here to leave the results in "order they came out of the database" order, which in some databases, is the order they were last modified in.

User · Answer

If you do not want to arrange the table in ascending or descending order  Use this   select   from table limit 5 offset  select count    from table  - 5

User · Answer

There is a handy trick that works in some databases for ordering in database order,

SELECT * FROM TableName ORDER BY true

Apparently, this can work in conjunction with any of the other suggestions posted here to leave the results in "order they came out of the database" order, which in some databases, is the order they were last modified in.

User · Answer

select   from table limit 5 offset  select count    from table  - 5

User · Answer

i am using this code   select   from tweets where placeID     placeID  and id  gt         select count    from tweets where placeID     placeID  -2

User · Answer

If you know how many rows there will be in total you can use the ROW NUMBER   function  Here s an examble from MSDN  http   msdn microsoft com en-us library ms186734 aspx   USE AdventureWorks  GO WITH OrderedOrders AS       SELECT SalesOrderID  OrderDate      ROW NUMBER   OVER  ORDER BY OrderDate  AS  RowNumber      FROM Sales SalesOrderHeader     SELECT    FROM OrderedOrders  WHERE RowNumber BETWEEN 50 AND 60

User · Answer

This is just about the most bizarre query I ve ever written  but I m pretty sure it gets the  last 5  rows from a table without ordering   select    from issues where issueid not in       select top            select count    from issues  - 5       issueid     from issues     Note that this makes use of SQL Server 2005 s ability to pass a value into the  top  clause - it doesn t work on SQL Server 2000

User · Answer

Well  the  last five rows  are actually the last five rows depending on your clustered index   Your clustered index  by definition  is the way that he rows are ordered  So you really can t get the  last five rows  without some order   You can  however  get the last five rows as it pertains to the clustered index   SELECT TOP 5   FROM MyTable ORDER BY MyCLusteredIndexColumn1  MyCLusteredIndexColumnq       MyCLusteredIndexColumnN DESC

User · Answer

You can retrieve them from memory  So first you get the rows in a DataSet  and then get the last 5 out of the DataSet

User · Answer

In SQL Server 2012 you can do this    Declare  Count1 int    Select  Count1   Count    FROM     Log  AS L  SELECT        FROM     Log  AS L ORDER BY L id OFFSET  Count - 5 ROWS FETCH NEXT 5 ROWS ONLY

User · Answer

Suppose you have an index on id  this will be lightning fast   SELECT   FROM  MyTable  WHERE  id   gt   SELECT MAX  id   - 5 FROM  MyTable

User · Answer

You can retrieve them from memory  So first you get the rows in a DataSet  and then get the last 5 out of the DataSet

User · Answer

The way your question is phrased makes it sound like you think you have to physically resort the data in the table in order to get it back in the order you want  If so  this is not the case  the ORDER BY clause exists for this purpose  The physical order in which the records are stored remains unchanged when using ORDER BY  The records are sorted in memory  or in temporary disk space  before they are returned   Note that the order that records get returned is not guaranteed without using an ORDER BY clause  So  while any of the the suggestions here may work  there is no reason to think they will continue to work  nor can you prove that they work in all cases with your current database  This is by design - I am assuming it is to give the database engine the freedom do as it will with the records in order to obtain best performance in the case where there is no explicit order specified   Assuming you wanted the last 5 records sorted by the field Name in ascending order  you could do something like this  which should work in either SQL 2000 or 2005   select Name  from       select top 5 Name      from MyTable      order by Name desc   a  order by Name asc

User · Answer

There is a handy trick that works in some databases for ordering in database order,

SELECT * FROM TableName ORDER BY true

Apparently, this can work in conjunction with any of the other suggestions posted here to leave the results in "order they came out of the database" order, which in some databases, is the order they were last modified in.

User · Answer

DECLARE  MYVAR  NVARCHAR 100  DECLARE  step  int SET  step   0    DECLARE MYTESTCURSOR CURSOR DYNAMIC  FOR SELECT col FROM  dbo   table  OPEN MYTESTCURSOR FETCH LAST FROM MYTESTCURSOR INTO  MYVAR print  MYVAR    WHILE  step  lt  10 BEGIN        FETCH PRIOR FROM MYTESTCURSOR INTO  MYVAR         print  MYVAR          SET  step    step   1  END    CLOSE MYTESTCURSOR DEALLOCATE MYTESTCURSOR

User · Answer

Search 5 records from last records you can use this   SELECT   FROM   Table Name WHERE  ID  lt   IDENT CURRENT  Table Name   AND ID  gt   IDENT CURRENT  Table Name   - 5

User · Answer

i am using this code   select   from tweets where placeID     placeID  and id  gt         select count    from tweets where placeID     placeID  -2

User · Answer

When number of rows in table is less than 5 the answers of Matt Hamilton and msuvajac  is Incorrect  Because a TOP N rowcount value may not be negative   A great example can be found Here

User · Answer

You can retrieve them from memory  So first you get the rows in a DataSet  and then get the last 5 out of the DataSet

User · Answer

This is just about the most bizarre query I ve ever written  but I m pretty sure it gets the  last 5  rows from a table without ordering   select    from issues where issueid not in       select top            select count    from issues  - 5       issueid     from issues     Note that this makes use of SQL Server 2005 s ability to pass a value into the  top  clause - it doesn t work on SQL Server 2000

User · Answer

In SQL Server 2012 you can do this    Declare  Count1 int    Select  Count1   Count    FROM     Log  AS L  SELECT        FROM     Log  AS L ORDER BY L id OFFSET  Count - 5 ROWS FETCH NEXT 5 ROWS ONLY

User · Answer

Without an order  this is impossible   What defines the  bottom    The following will select 5 rows according to how they are stored in the database   SELECT TOP 5   FROM  TableName

[sql-server] How do I select last 5 rows in a table without sorting?

Examples related to sql-server

Examples related to tsql