Opposite of TOP in SQL Server

Question

I need to retrieve the last few entries from a table. I can retrieve them using:

SELECT TOP n * 
FROM table 
ORDER BY id DESC 

That I looked everywhere and that's the only answer I could find, But that way I get them in reverse order. I need them in the same order as they are in the table because it's for a messaging interface.

Answer 1

I suggest you to use a ROW_NUMBER() like this:

SELECT *
FROM (
    SELECT 
        *, ROW_NUMBER() OVER (ORDER BY id DESC) AS RowNo
    FROM 
        yourTable
     ) AS t
WHERE
    (RowNO < @n)
ORDER BY
    id

Answer 2

Use a derived table:

select id, ...
from
(
 select top n id, ...
 from t
 order by id desc
) dt
order by id