Limit Rows Returned In Access Query

hi there

I am executing a query which will return some summary data from a table in Access. I have built the query with the idea of driving an Access report, but I want to insert a limit the rows returned to the first 20 rows. I have also tried WHERE ROWNUM < 21, but using it into the SQL of the Access query does not work. What can I use here to limit the number of rows returned? This is running in Access and not ASP. The SQL is included below:

SELECT TBL_D02_PROG_4_SUMMARY.CUST_ID, TBL_D02_PROG_4_SUMMARY.NAME1, Sum(TBL_D02_PROG_4_SUMMARY.QTY_ORDERED) AS TOT_ORDERED, Sum(TBL_D02_PROG_4_SUMMARY.ORDER_TOTAL_PRICE) AS SUM_PRICE
FROM TBL_D02_PROG_4_SUMMARY
GROUP BY TBL_D02_PROG_4_SUMMARY.CUST_ID, TBL_D02_PROG_4_SUMMARY.NAME1
ORDER BY Sum(TBL_D02_PROG_4_SUMMARY.QTY_ORDERED) DESC;

Please help me regarding this

your views will be appreciated

SELECT TOP 20 TBL_D02_PROG_4_SUMMARY.CUST_ID, TBL_D02_PROG_4_SUMMARY.NAME1, Sum(TBL_D02_PROG_4_SUMMARY.QTY_ORDERED) AS TOT_ORDERED, Sum(TBL_D02_PROG_4_SUMMARY.ORDER_TOTAL_PRICE) AS SUM_PRICE
FROM TBL_D02_PROG_4_SUMMARY
GROUP BY TBL_D02_PROG_4_SUMMARY.CUST_ID, TBL_D02_PROG_4_SUMMARY.NAME1
ORDER BY Sum(TBL_D02_PROG_4_SUMMARY.QTY_ORDERED) DESC;

SELECT TOP 5 column1, column2, dt FROM foo ORDER BY dt DESC


For SQL Server 6.5, you'll have to take a slightly different approach, utilizing the SET ROWCOUNT functionality (this will not work in Access):
SET ROWCOUNT 5
SELECT column1, column2, dt FROM foo ORDER BY dt DESC
SET ROWCOUNT 0 -- (This last step is the most important!

It isn't something that is available at the configuration level. You may want to double check that it does what you want it to do anyway: see the javadoc for setMaxRows. With Oracle it is still going to fetch every row back for the query and then just drop the ones outside the range. You would really need to use rownum to make it work well with Oracle and you can't do that either in the configuration.

If you know you will be dealing with only one table, then define a view with rownum in the where statement to limit the number of rows. In this way, the number of rows is controlled at the DB and does not need to be specified as part of any query from a client application. If you want to change the number of rows returned, then redefine the view prior to executing query.

A more dynamic method would be to develop a procedure and pass in a number of rows, and have the procedure return a ref_cursor to your client. This would have the advantage of avoiding hard parsing on the DB, and increase performance.