Inverted select TOP *

Question

do you know when you have that huge log table and you just need to see the last X rows to know what is going on at the time?

usually you can do:

select top 100 * 
from log_table
order by ID desc

to show the 100 newest records, but it will do on the inverse order (of course, because of the order by DESC), for example:

100010
100009
100008
and so on..

but for the sake of simplicity I would like to see the records on the order they happened. I can do that by running this query:

select * 
from(
    select top 100 * from log_table order by ID desc
    ) a
order by a.id

where I get my top 100 order by ID desc and then invert the result set. It works but it seems kin of unnecessary to run 2 select to produce this result.

My question is: does anyone have a better idea of doing that? Like a select top on the end of the table?

EDIT: execution plan of both queries: It seems like Alex's idea is very good but David was also right, there is only one select and one sort

EDIT2: set statistics IO ON:

(10 row(s) affected)
Table 'sysdtslog90'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 12, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

(10 row(s) affected)
Table 'sysdtslog90'. Scan count 2, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 12, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

Answer 1

If id is indexed and sequential enough the fastest way is probably;

select * from log_table where id > (select max(id) from log_table) - N

An explicit order by is still required to guarantee the order however.

Answer 2

but it seems kin of unnecessary to run 2 select to produce this result.

Wrong. It is necessary.

More detail: Look at the estimated execution plan of your query. It probably looks like ClusteredIndexScan -> Top -> only one Sort. The inner query's OrderBy doesn't perform a Sort, it just directs the execution to read from the "back" of the table.