Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 25-02-2009
Member
 
Join Date: Nov 2008
Posts: 69
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
Reply With Quote
  #2  
Old 25-02-2009
Member
 
Join Date: May 2008
Posts: 2,383
Re: Limit Rows Returned In Access Query

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;
__________________
The FIFA Manager 2009 PC Game
Reply With Quote
  #3  
Old 25-02-2009
Member
 
Join Date: Feb 2008
Posts: 1,848
Re: Limit Rows Returned In Access Query

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!
Reply With Quote
  #4  
Old 25-02-2009
Member
 
Join Date: Jan 2008
Posts: 1,515
Re: Limit Rows Returned In Access Query

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.
Reply With Quote
  #5  
Old 25-02-2009
Member
 
Join Date: Apr 2008
Posts: 1,948
Re: Limit Rows Returned In Access Query

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.
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Limit Rows Returned In Access Query"
Thread Thread Starter Forum Replies Last Post
How to run Query Automatically on MS Access Rixwel Software Development 3 18-08-2009 10:24 AM
Number of rows returned by SQL query odumitre Software Development 2 12-08-2009 05:11 PM
How to limit internet access windows xp ? Yatish Technology & Internet 3 11-07-2009 01:28 PM
Merging duplicate names not rows copy rows into new workbook each time VBA Excel 2003 awkwardsmile Software Development 2 09-07-2009 08:57 AM
Help in Access query $tatic Software Development 4 20-12-2008 05:38 PM


All times are GMT +5.5. The time now is 06:23 AM.