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.
