Re: Need Help with PL/SQL

From: Mark Styles <lambic_at_msn.com>
Date: 1996/10/28
Message-ID: <3275018C.39B1_at_msn.com>#1/1


atyagi_at_mc.xerox.com wrote:
>have written this query and I want to restrict the total number
>of records returned to exactly 100. I am trying and I can not make
>it work. I will greatly appreciate any suggestion.
>
> SELECT EO_SHIPPED.SHIP_DATE, COUNT(EO_SHIPPED.ORDERNUM)
> FROM WHISC.EO_SHIPPED EO_SHIPPED
> WHERE (EO_SHIPPED.SHIP_DATE >=ADD_MONTHS(SYSDATE,-5))
> AND (TO_CHAR(EO_SHIPPED.SHIP_DATE,'DAY') NOT LIKE 'S%')
> GROUP BY SHIP_DATE;
Well, luckily for you, you aren't using an ORDER BY clause, so you can just and a bit to your where clause, so it becomes:

WHERE (EO_SHIPPED.SHIP_DATE >=ADD_MONTHS(SYSDATE,-5))

      AND (TO_CHAR(EO_SHIPPED.SHIP_DATE,'DAY') NOT LIKE 'S%')
      AND ROWNUM <= 100

Be careful with this if you use ORDER BY though, because ROWNUM is assigned BEFORE the sort takes place! Received on Mon Oct 28 1996 - 00:00:00 CET

Original text of this message