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.

Answer 1

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.

Answer 2

Suppose you have an index on id, this will be lightning fast:

SELECT * FROM [MyTable] WHERE [id] > (SELECT MAX([id]) - 5 FROM [MyTable])

Answer 3

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

Answer 4

1. You need to count number of rows inside table ( say we have 12 rows )
2. then subtract 5 rows from them ( we are now in 7 )

3. select * where index_column > 7

   select * from users
   where user_id > 
   ( (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.

Answer 5

select * from table limit 5 offset (select count(*) from table) - 5;

Answer 6

Search 5 records from last records you can use this,

SELECT *
FROM   Table Name
WHERE  ID <= IDENT_CURRENT('Table Name')
AND ID >= IDENT_CURRENT('Table Name') - 5

Answer 7

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]

Answer 8

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

Answer 9

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;

Answer 10

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;

Answer 11

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 

Answer 12

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

Answer 13

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.

Answer 14

i am using this code:

select * from tweets where placeID = '$placeID' and id > (
    (select count(*) from tweets where placeID = '$placeID')-2)

Answer 15

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

Answer 16

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.

Answer 17

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.

Answer 18

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*/

Answer 19

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 < 10
BEGIN   
    FETCH PRIOR FROM MYTESTCURSOR INTO @MYVAR
        print @MYVAR;
        SET @step = @step + 1;
END   
CLOSE MYTESTCURSOR
DEALLOCATE MYTESTCURSOR

Answer 20

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

Answer 21

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)

Answer 22

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;