SQL Select Bottom Records

Question

I have a query where I wish to retrieve the oldest X records. At present my query is something like the following:

SELECT Id, Title, Comments, CreatedDate
FROM MyTable
WHERE CreatedDate > @OlderThanDate
ORDER BY CreatedDate DESC

I know that normally I would remove the 'DESC' keyword to switch the order of the records, however in this instance I still want to get records ordered with the newest item first.

So I want to know if there is any means of performing this query such that I get the oldest X items sorted such that the newest item is first. I should also add that my database exists on SQL Server 2005.

Answer 1

Why not just use a subquery?

SELECT T1.* 
FROM
(SELECT TOP X Id, Title, Comments, CreatedDate
FROM MyTable
WHERE CreatedDate > @OlderThanDate
ORDER BY CreatedDate) T1
ORDER BY CreatedDate DESC

Answer 2

Embed the query. You take the top x when sorted in ascending order (i.e. the oldest) and then re-sort those in descending order ...

select * 
from 
(
    SELECT top X Id, Title, Comments, CreatedDate
    FROM MyTable
    WHERE CreatedDate > @OlderThanDate
    ORDER BY CreatedDate 
) a
order by createddate desc