Limit Function in MS SQL?

[Yahweh]

MySQL has a convenient syntax for paging data that looks like this:

SELECT * FROM MyTable LIMIT 10, 20

That would select 10 records, starting from record 20, so that it returns records 20 - 30. This is convenient way to page data, without returning anymore rows than than you need.However, MS SQL doesn't appear to support that syntax. What is the equivalent sql code to select any N rows from an arbitrary starting point, without having to create a stored procedure?Thanks in advance

[aspnetguy]

check this outhttp://www.thescripts.com/forum/thread80100.html

[Yahweh]

check this outhttp://www.thescripts.com/forum/thread80100.html

<{POST_SNAPBACK}>

So if wanted to select rows 10000 to 10020, I'd have to use something like this:

select top 10 * from(select top 10020 *from tblContactorder by LastName asc) dtorder by LastName desc

I think it would be murder on my site site to be constantly selecting 10s of 10000s, or even millions of rows, when I only need 10.

[aspnetguy]

I am not sure but I thinnk that is what the mysql version does behind the scenes, it just gives you an easy way to request it. I may be wrong tho.Yes I agree...this is not a very good method for large databases, but it is the ONLY method I can find.The only solution I can offer is an alternative is to have a column in your table called rownum...you can increment it with each entry then you can use it in the where condition