how to do the same thing in mssql as mysql?

in MySql:

SELECT * FROM table LIMIT 5,10

How to do this in MicroSoftSql?

What does this do ? Does it select 10 rows from row 5 onwards ? If so, you can do something like this

select top 5 * from

(

select top 15 from table ORDER BY id ASC

)

ORDER BY id DESC

assuming that you have an index on the column called id. Of course, you need to add a third query to flip them back to ascending order, if you require that.

|||

but what if I have more than 100000 records in the table?

if I want to get 50000-50005

select top 50000 * from

(

select top 5 from table ORDER BY id ASC

)

ORDER BY id DESC

so should it be very slow?

|||

I'm not sure how big our database was, but I used this trick to do paging on a system and it was fine. That was a few years ago, SQL Server 2005 may have some tricks up it's sleeve that I don't know and didn't find in the help just now.

|||

I would like to suggest Gates, Next Version MSSQL and Access append "Limit" sentence.

*^_^!

|||

http://davidhayden.com/blog/dave/archive/2005/12/30/2652.aspx

Looks like there's a new way to get row numbers. I thought there may be, but my initial search did not find it.

|||

oh... thanks,

but is there a way to support Access?

Because our develop system base on asp.net+Access

|||

*grin* then you should have asked about access to start with :-)

I'd expect my first solution to work with Access, but I couldn't be sure, I don't use it.