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,

Neilnzanella@.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|||(nzanella@.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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

limited resultset without SP

I have a search query on my page. However, since the resultset may be very large, I want to retreive only those results that are currently shown on the gridview page.
Often a user won't browse through more than a couple of pages, so it's b-*** to send all the records to the client.
I know that with a sp you can define that you want to retrieve the first or second x records, but I want to do this with SQL...is that possible?You could put a TOP 100 or something in your search query and page them at the client side or if you want to use SQL to do the paging, you'd be doing the search over and over again for each page. You *could* get a few duplicate records. since, I am assuming you'd do a top 10 or 20 from the query to show them on the page and when you do the search again, you are starting fresh and there isnt an easy way to remember the previous search. unles you store the result set you first got into some table (you could put the spid in one column) and use the spid to get the next result set. Obviously you'd have to worry about cleaning up the table periodically in such a case.