SQL Query to select last X records
Problem: I need an SQL query (in MS SQL) to select last X records, for example my table has 1000 records, I want to select last 10 records.
I know by using TOP I can do that, but it returns from first X records, not last X records.
Also I do not want to use DESC ordering.
Queries like: SELECT TOP 10 * FROM mytable ORDER BY field1 DESC
is not desired...
And also I need to select some records in middle, e.g. third ten records.
How can I perform these actions using MS SQL. (Using MySQL, I think there is a keyword LIMIT to do that, but isn't working in MS SQL)

Solution:
declare @i integer
declare @row_id uniqueidentifier

create table #temp_table (row_id uniqueidentifier)
set @i=0

declare cOf scroll cursor for
select row_id from myTable
open cof

fetch last from cOf into @row_id
while @i < 10
begin
insert into #temp_table values (@row_id)
fetch prior from cof into @row_id
set @i = @i+1
end
close cof
deallocate cof

select * from myTable where row_id in (select * from #temp_table)

drop table #temp_table

You can also modify the above code to get sets of records from any part of the table.