LIMIT in Queries

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.

--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com

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!

*************************************************

"Jonathan Wood" <jwood (AT) softcircuits (DOT) com> wrote

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.

--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com

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"

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.

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

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.

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!

--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com

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
SoftCircuits Programming
http://www.softcircuits.com


"Andrew Backer" <awbacker (AT) gmail (DOT) com> wrote

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"

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.