Re: Need Help with PL/SQL
Date: 1996/10/30
Message-ID: <3276c21f.4247988_at_dcsun4>#1/1
On Mon, 28 Oct 1996 10:55:08 -0800, Mark Styles <lambic_at_msn.com> wrote:
>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!
Rownum is assigned before any aggregate operation like "order by", "group by" takes place. The rownum will be applied before the group by. the answer is (in 7.1 and up)
select * from (
>> 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
)
where rownum <= 100
Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com
- Check out Oracle Governments web site! -----
Follow the link to "Tech Center"
and then downloadable Utilities for some free software...
statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Wed Oct 30 1996 - 00:00:00 CET