SELECT TOP 50 * returns more than 50 rows

  • Thread starter Thread starter kmhuntly
  • Start date Start date
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.
 
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."
 
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 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?
Click to expand...

If you include the Primary Key in the order by clause then there will never be a
tie.
 
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.
Click to expand...

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.

--
 
Back
Top