Board index » delphi » how to limit query size.

how to limit query size.

I am using ADO in an isapi dll. I have a table with about 2000 recs. I want
just the first 10 records back. I thought that if I set maxrecords to 10,
that the tadoquery object would limit the query size to 10 records even
though my sql statement said "select * from users". Should this work or am I
stupid ? (if you dont know the answer to part one "shouldn't this work" ,
then you are not entitled to answer part 2 "am I stupid", besides I already
know the answer, my wife reminds me of it daily.)
Whats the best way to get the first N records from a very large database
table without reading the whole table ?  I thought that there was a TOP
predicate for sql that would use the index if the column you were test for
had an index but I cant seem to find it and I'm cut off from my reference
books.

TKS

Del

 

Re:how to limit query size.


Hi,

Quote
>I thought that there was a TOP
> predicate for sql that would use the index if the column you were test for
> had an index but I cant seem to find it and I'm cut off from my reference
> books.

There is on in Access , according to this excerpt taken at this adress:

http://msdn.microsoft.com/library/techart/acintsql.htm

"The TOP keyword
The TOP keyword is used to return a certain number of rows that fall at the
top or bottom of a range that is specified by an ORDER BY clause. The ORDER
BY clause is used to sort the rows in either ascending or descending order.
If there are equal values present, the TOP keyword will return all rows that
have the equal value. Let's say that we want to determine the highest three
invoice amounts in our invoices database. We'd write a statement like this:

SELECT TOP 3 InvoiceDate, Amount
   FROM tblInvoices
   ORDER BY Amount DESC

We can also use the optional PERCENT keyword with the TOP keyword to return
a percentage of rows that fall at the top or bottom of a range that is
specified by an ORDER BY clause. The code looks like this:

SELECT TOP 25 PERCENT InvoiceDate, Amount
   FROM tblInvoices
   ORDER BY Amount DESC

Note that if you do not specify an ORDER BY clause, the TOP keyword will not
be helpful: it will return a random sampling of rows.

For more information about predicates, type all, distinct predicates in the
Office Assistant or on the Answer Wizard tab in the Microsoft Access Help
window, and then click Search."

You don't indicate which database you are querying or which provider you are
using, I hope this can be useful though.

Thrse

Re:how to limit query size.


Quote
>I am using ADO in an isapi dll. I have a table with about 2000 recs. I want
>just the first 10 records back. I thought that if I set maxrecords to 10,
>that the tadoquery object would limit the query size to 10 records even
>though my sql statement said "select * from users". Should this work or am I
>stupid ? (if you dont know the answer to part one "shouldn't this work" ,

It works for me with MSSQL 7 but my guess is it depends on what provider you are
using.

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Other Threads