Jump to content

Limit Function in MS SQL?


Yahweh
 Share

Recommended Posts

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 :)

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...