Select Top N Records Ordered by X, But Have Results in Reverse Order

Question

I'm trying to get the top N records (when ordered by some column X), but have the result set in reverse order. The following statement is incorrect, but probably demonstrates what I'm after:

SELECT * FROM (SELECT TOP 10 * FROM FooTable ORDER BY X DESC) ORDER BY X ASC

For example, column X could be an ID or a timestamp; I want the latest 10 records but want them returned in forward chronological order.

Answer 1

SELECT * FROM 
   (SELECT TOP 10 * FROM FooTable ORDER BY X DESC) as myAlias 
ORDER BY X ASC 

i.e. you might need an alias on your subquery, but other than that it should work.

Answer 2

Try

SELECT * FROM 
(SELECT TOP 10 * FROM FooTable ORDER BY X DESC) temp  --alias
ORDER BY X

or with a common table expression (CTE)

WITH Temp AS (SELECT TOP 10 * FROM FooTable ORDER BY X DESC) 

SELECT * FROM temp 
ORDER BY X

Answer 3

ORDER BY clause is used to order the RESULT SET by a specified column.

Your query Select TOP 10 * from FooTable ORDER BY X DESC assuming X is the timestamp ,is not going to return the most recently inserted 10 rows. It will return the top 10 rows as stored (in whichever order) in the Database, and will then return the result set of the 10 such rows , in descending order. Hence your subquery should be modified to

Select TOP 10 * from (Select * from FooTable ORDER BY DESC) as T

This should fulfill your first requirement. You may then use this result set as an alias, to decide your final sort order.

I hope i have understood you correctly, when you say "I'm trying to get the top N records (when ordered by some column X)"