SELECT TOP 50 * returns more than 50 rows

K

kmhuntly

I have a VB6 app that interfaces with an access database. The access
database has about 100,000 rows or so, and I want to limit the
resultset to 50 rows at a time. I have an SQL statement that reads as
follows:

SELECT TOP 50 * FROM Table WHERE foo IS blah ORDER BY date ASC;

I am using ADO to make the connection and open the recordset, my ADO
options are adOpenKeyset and adLockOptimistic.

Each time this query is run from the application, the recordset
returned is consistently higher than 50, sometimes only by a few,
others by a few hundred. I've tried everything I can think of to
resolve this, but haven't been able to yet. The same query works as
desired in Access 2003 from an SQL window, and was working in the
application when it was Access 97 backing it.
 
D

Douglas J. Steele

From the Help file:

"The TOP predicate doesn't choose between equal values. In the preceding
example, if the twenty-fifth and twenty-sixth highest grade point averages
are the same, the query will return 26 records."
 
K

kmhuntly

If I had the choice (which I unfortunately don't) this app would be
interfacing with MySQL and I wouldn't have these headaches. Removing
the ORDER BY did of course fix the issue, but is there any way I can
acheive the same results using different code?
 
R

Rick Brandt

If I had the choice (which I unfortunately don't) this app would be
interfacing with MySQL and I wouldn't have these headaches. Removing
the ORDER BY did of course fix the issue, but is there any way I can
acheive the same results using different code?

If you include the Primary Key in the order by clause then there will never be a
tie.
 
J

Jamie Collins

I have a VB6 app that interfaces with an access database. The access
database has about 100,000 rows or so, and I want to limit the
resultset to 50 rows at a time.

It sounds like you want to perform paging. If this is the case, take a
look at the ADO Recordset object's PageSize and AbsolutePage
properties. This article, although written for SQL Server, may help:

How do I page through a recordset?
http://www.aspfaq.com/show.asp?id=2120

Jamie.

--
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top