...

View Full Version : MS access query



ellisd5
12-16-2003, 05:19 PM
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

Roy Sinclair
12-16-2003, 06:35 PM
SELECT TOP 20
value1, value2
FROM
myTable
ORDER BY
value2 desc

jeskel
12-16-2003, 10:39 PM
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...

raf
12-17-2003, 12:04 AM
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).

glenngv
12-17-2003, 02:16 AM
You need recordset paging (http://www.asp101.com/articles/recordsetpaging/index.asp) like jeskel suggested.

jeskel
12-17-2003, 01:23 PM
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

ellisd5
12-17-2003, 03:06 PM
Thanks for all the help, I have it working how I want now :thumbsup:



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum