470,855 Members | 1,301 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,855 developers. It's quick & easy.

limiting result set from MS SQL Server Query


Hello,

I am running SQL Server 2000. I would like to know whether
Microsoft Transact-SQL has a method for limiting the result
set from a query in a way analogous to MySQL's LIMIT keyword,
so that, for instance, if the result set contains 10,000 rows,
then only the first 10 rows from the record set are output.

Thank you,

Best Regards,

Neil

Jul 23 '05 #1
2 2322

nz******@cs.mun.ca wrote:
Hello,

I am running SQL Server 2000. I would like to know whether
Microsoft Transact-SQL has a method for limiting the result
set from a query in a way analogous to MySQL's LIMIT keyword,
so that, for instance, if the result set contains 10,000 rows,
then only the first 10 rows from the record set are output.


Yes. You need to use the TOP clause.

In your example:

SELECT TOP 10 col1, col2, col3 FROM TABLE

Jul 23 '05 #2
(nz******@cs.mun.ca) writes:
I am running SQL Server 2000. I would like to know whether
Microsoft Transact-SQL has a method for limiting the result
set from a query in a way analogous to MySQL's LIMIT keyword,
so that, for instance, if the result set contains 10,000 rows,
then only the first 10 rows from the record set are output.


As Andrew said you can use TOP. For it to be meaningful, you need in
most cases also use an ORDER BY clause.

If you want to implement paging, there are a couple of options. One is
to use a @last_key variable, and to:

SELECT TOP 10 ... FROM tbl WHERE keycol = @last_key ORDER BY keycol

To implement arbitrary jumps, you can do something like:

CREATE TABLE #temp (ident int IDENTITY,
....
)

and then

INSERT #temp (...)
SELECT ....

And to get rows 41 to 60

SELECT ... FROM #temp WHERE ident BETWEEN 41 AND 60
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Troy Hakala | last post: by
5 posts views Thread by Krisnamourt Correia via SQLMonster.com | last post: by
4 posts views Thread by Bob Bedford | last post: by
9 posts views Thread by JRough | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.