For getting a particular row number from a table

SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY name) as row FROM sys.databases ) a WHERE row > 5 and row <= 10



The query above returns rows 6 through 10 from sys.databases as ordered by the "name" column. ROW_NUMBER() is the key function we're using here. It's one of a set of ranking functions introduced in 2005. Note that it's always accompanied by an OVER clause that specifies the ordering that the row_number should be based on.


U can find the above query in the blow link
http://blogs.msdn.com/sqlserver/archive/2006/10/25/limit-in-sql-server.aspx