Select the Top N Records ordered by X, but get the results in the reverse order.

Question

I am trying to get the top N records (when sorting by some columns of X), but the result is in the reverse order. The following instruction is incorrect , but probably demonstrates what I need:

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

For example, column X may be an identifier or a timestamp; I want the last 10 entries, but I want them to be returned in direct chronological order.

Answer 1

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

i.e. you may need an alias in 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

The ORDER BY clause is used to order SET RESULT in the specified column.

Your query Select TOP 10 * from FooTable ORDER BY X DESC Assuming X is a timestamp, it will not return the last 10 rows inserted. It will return the top 10 rows as stored (in any order) in the database and then will return a result set of 10 such rows in descending order. Therefore, your subquery must be changed to

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

This should meet your first requirement. You can then use this result set as an alias to determine your final sort order.

I hope I understood you correctly when you say: "I'm trying to get the top N records (when ordering with some X columns)"

Answer 4

An alternative solution to this issue for all unsupported versions for the TOP keyword is to use LIMIT . Example: -

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