Friday, December 19, 2003

Listing records from 10 to 15 (for ex) without using where clause

The below query lists the records from 10 to 15 from a table. The logic is we need to fetch the first 15 records from the table then take the first 6 records within it in the DESCending order. Hope the logic is pretty straight forward!!

Select * From
    (
    Select TOP 6 * From -- (A1)
        (
            Select TOP 15 * from employeeTable order by Sno --- (A2)
        )
        as D1 ORDER BY Sno DESC
    )
    as D2 ORDER BY Sno


Let us assume that the field sno has values from 1,2,3,4..... 20. Part "A2" in the above sample would fetch 1,2,3,4....15. Now check part "A1" it fetches the first 6 records from this result in DESCending order. So the result would be 10,11,12,13,14,15.

No comments: