Database functions question

I was looking for similar functions between MS SQL 2000 and MySQL 3.X...

In mySQL you can select specific number of items with a "LIMIT" clause and also specify Starting record number and how many to display EX: Select * FROM myTable WHERE ... LIMIT 1, 50

So that would pull 50 records beginning with record number 1.

Is there anything similar to this in MS SQL? Or how would I be able to achieve this result

Thanks,
Lito

select TOP 50 * from mytable
ORDER BY something

unfortunately, there's no offset capability, so to get rows 51 - 100 is a lot harder

rudy
http://r937.com/

thanks rudy,
but offset capability is exactly what I need! This is a web based app...

the trouble is my database contains records for multiple users and those records are only available to the owner and they are scattered throuout the database in no particular order also the owner needs to be able page through his records in order.

a google search for "record paging sql server" produced these hits:

http://www.aspfree.com/articles/1417,1/articles.aspx
(part of the dev shed network)

http://rosca.net/writing/articles/serverside_paging.asp

http://www.4guysfromrolla.com/webtech/062899-1.shtml
http://www.4guysfromrolla.com/webtech/072101-1.shtml

http://searchvb.techtarget.com/tip/...i929053,00.html

http://rtfm.atrax.co.uk/infinitemon...les/sql/996.asp

http://www.adopenstatic.com/experim...dsetpaging2.asp

http://www.planet-source-code.com/v...&txtCodeId=6252

Thanks r937,
I have see some of these pages but not all, I was just hoping that there is something equivalent to LIMIT function in MySQL, I guess not then.

In case anyone else is looking for a solution this seems to work pretty good...

It's short and easy compared to some others out there

First select statement tells how many records per page
in my case it's one record per page, if you choose multiple records per page for example 10 records on each page... then you must also increment second select by 10

ID is a unique identifier
userID in my case is used to identify the data that belongs to specific user (no one but user 101 is allowed to see it)

in Second select it tells MS SQL to to find the top record that is not in the first 3 records with smallest ID (that's where ORDER BY comes in handy)

so then you would get 4th record with smallest ID

for example...
if user 101 has following ID's in database (12,550,725,1200,15000) then
the query above would return record with ID field 1200

and if you want to go a page back
you just subtract from ID in the second select statement the number of records per page.

~Lito