MS access query

I do a select of my access database and bring back all the records. So currently like this

SELECT
value1, value2
FROM
myTable
ORDER BY
value2 desc


What I want to add to this is only returning 20 rows, how can I do this?

Thanks Dale

SELECT TOP 20
value1, value2
FROM
myTable
ORDER BY
value2 desc

Originally posted by hharchester

What I want to add to this is only returning 20 rows, how can I do this?

Just incase: do you mean the 20 top rows or only 20 rows per page (like 1-20; 21-40; 41-60 and so on)? In that case it is a recodset paging that you're trying to do... It is a bit more complicated but not that much...

By using TOP 20 , there is absolutely no guaranty that you wount get more then 20 records.
The reason is that TOP doesn't cut off if you have equal values for the 20°, 21°,22° etc values.

So if you would have a value2 distribution like

1,1,1,1,1,1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,3,3,3,...

then you would get 34 records or so, not 20.

If you realy need a maximum of 20 records, then you need an additional loop inside your ASP code, where you loop through the records until you reach the 20° (using an incrementing counter-variable).

You need recordset paging (http://www.asp101.com/articles/recordsetpaging/index.asp) like jeskel suggested.

the ASP101 code is excellent... I started with this one. However, you might benefit from the getrows method. Check this thread and the two links provided by raf: http://www.codingforums.com/showthread.php?s=&threadid=29360

Thanks for all the help, I have it working how I want now :thumbsup: