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

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

i am using this code:

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

Answer 2

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 3

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 4

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 5

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

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

Answer 6

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.