Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql

selection

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.

like image 870
I. J. Kennedy Avatar asked Apr 03 '10 20:04

I. J. Kennedy


2 Answers

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.

like image 190
davek Avatar answered Oct 20 '22 00:10

davek


An alternate solution to this question for all non-supported versions for TOP keyword is to use LIMIT. Example :-

SELECT * FROM 
   (SELECT * FROM FooTable ORDER BY X DESC LIMIT 10) as myAlias 
ORDER BY X ASC 
like image 21
Sunny Avatar answered Oct 20 '22 01:10

Sunny