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

Answer 3

thanks dude it helped

But wht is I m using joints in the table. It didn't worked for me.

Any help wolud be appreciated ----Kalpesh Joshi

Answer 4

Kalpesh, it does work, I have just used the example by Jason Punyon and it works great and my example has a join in it:

    SELECT Bottom.* from 
    (    
        SELECT TOP(@numRecords) Filter.Pk, Images.Md5, 
           FROM Images
        JOIN Filter ON Images.Pk = Filter.ImagePk AND Images.CasePk = Filter.CasePk
           WHERE Filter.Pk < @recordPk
           AND Filter.CasePk = @casePk
           AND Filter.UserPk = @userPk
        ORDER BY Filter.Pk desc) Bottom ORDER BY Bottom.Pk ASC