Help writing SQL Query

Results 1 to 2 of 2

Thread: Help writing SQL Query

  1. #1
    Join Date
    Dec 1969

    Default Help writing SQL Query

    Hi,<BR><BR>I have Oracle table with several columns. One of the fields is Order_Date.<BR><BR><BR>Order_Date<BR>24-OCT-2000<BR>23-NOV-2000<BR>08-DEC-2000<BR>10-JAN-2001<BR>04-MAR-2001<BR>05-APR-2001<BR>05-JUL-2001<BR>11-NOV-2001<BR>09-DEC-2001 <BR><BR><BR>I need to write a query which returns previous six orderdates (including<BR>the date passed). For instance, if I pass 09-DEC-2001, the query should<BR>return<BR><BR>09-DEC-2001<BR>11-NOV-2001<BR>05-JUL-2001<BR>05-APR-2001<BR>04-MAR-2001<BR>10-JAN-2001<BR><BR>And if I pass 05-APR-2001, the query should return<BR><BR>05-APR-2001<BR>04-MAR-2001<BR>10-JAN-2001<BR>08-DEC-2000<BR>23-NOV-2000<BR>24-OCT-2000<BR><BR>I would appreciate if somebody could help to write the query.<BR><BR>Thanks

  2. #2
    Join Date
    Dec 1969

    Default In Oracle, huh?

    The "trick" is to find what keyword that Oracle adds to standard SQL that allows you to limit the output.<BR><BR>Basically, what you want to do is<BR><BR>SELECT TOP 6 * FROM table <BR>WHERE orderDate &#060;= &#039;05-APR-2001&#039; <BR>ORDER BY orderDate DESC<BR><BR>Where "TOP 6" means just return the first 6 records that meet the WHERE and ORDER BY qualifications.<BR><BR>That syntax works for SQL Server and Access, but MySQL (to pick another example) uses LIMIT, and in a somewhat different way:<BR><BR>SELECT * FROM table <BR>WHERE orderDate &#060;= &#039;05-APR-2001&#039; <BR>ORDER BY orderDate DESC<BR>LIMIT 0, 5<BR><BR>[meaning the zero-eth through fifth records].<BR><BR>But I dunno what Oracle uses for this. Surely, though, it has some similar capability.<BR><BR>******************<BR><BR>If worst comes to worst, you can use ADODB.RecordSet.MaxRecords to limit yourself to 6 records, but this *might not* prevent the DB engine from doing a lot more work than it needs to, so finding a SQL way to do it is preferable.<BR><BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts