LIMIT in Queries

  • Thread starter Thread starter Jonathan Wood
  • Start date Start date
J

Jonathan Wood

Does MS SQL support LIMIT?

I'd like to implement paging at the query level. Examples of this in a book
I have involve temporary tables and the like. Then I read something about
LIMIT.

Would LIMIT be the best way to implement paging?

I'm using MS SQL Express on my development computer. It doesn't appear to
support LIMIT. Does MS SQL?

Thanks.
 
No, but you can use TOP.

For example, to get the following:

SELECT column FROM table
LIMIT 10

You would use

SELECT TOP 10 column FROM table

You do not have OFFSET, however, so you have to use a WHERE clause and keep
tabs of beginning and end ids to select the next page. If you also allow
sorting, you will have to make your method of selecting flexible enough to
store pointers on each of the fields one can sort on.

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

*************************************************
| Think outside the box!
|
*************************************************
 
TOP will work, but if you are looking for OFFSET later then you are out of
luck.

This is one of those features that everyone has been begging for for years
now, as long as I have used mssql, but they won't give us. Probably because
of some NIH proplem, as usual, along with the customary smidgen of arrogance.
So we are stuck with this bastard child "top" :(
 
Cowboy,
No, but you can use TOP.

For example, to get the following:

SELECT column FROM table
LIMIT 10

You would use

SELECT TOP 10 column FROM table
Click to expand...

That implements paging as long as the user only wants to see the first page.
;-)
You do not have OFFSET, however, so you have to use a WHERE clause and
keep tabs of beginning and end ids to select the next page. If you also
allow sorting, you will have to make your method of selecting flexible
enough to store pointers on each of the fields one can sort on.
Click to expand...

I don't know how I'd limit it to start at a particular ID, but at any rate,
I do have some examples of paging with MS SQL and, as messy as they are, it
sounds like that's about as clean as MS SQL will do it.

Does anyone have a clue why MS never added such an easy syntax as LIMIT to
their SQL?

Thanks!
 
Seems like a serious limitation to me. Coupled with MS's inexplicable
resistance to correctly supporting CSS standards in their browser, it's
quite a brain teaser there.

Thanks.
 
Jonathan Wood said:
Cowboy,


That implements paging as long as the user only wants to see the first
page. ;-)
Click to expand...

Sure, but you can offset the data by using a WHERE clause. Yes, it is much
more involved.

SELECT TOP n * FROM Table
WHERE id > x

or

SELECT TOP n * FROM Table
WHERE id < x

As you move up and down in pages, you can build it up each direction. You
can even adopt this when going up many pages by using a temp table or
similar.

Not sure why MS did not include this.

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

*************************************************
| Think outside the box!
|
*************************************************
 
Back
Top