CODEWITHSUNDEEP
sqlsql-servert-sql
Richard McGuire
Richard McGuire

Reputation: 11090

SQL Select Bottom Records

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.

Upvotes: 10

Views: 19221

Answers (2)

Restore The Data Dumps Again
Restore The Data Dumps Again

Reputation: 39335

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

Upvotes: 22

Unsliced
Unsliced

Reputation: 10552

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

Upvotes: 1

Related Questions