"Rao, Maheswara" wrote:
>
> List,
>
> Following are two SQL statements. The first SQL statement works OK (where
> ROWNUM < 5 is used). The second SQL statement does not return any rows
> (where ROWNUM = 5 is used). This table contains 200 records.
>
> First SQL statement
> -------------------
> SELECT * FROM
> (SELECT PROCESSED_DATE
> FROM TRADES WHERE PROCESSED_STATUS = 0
> ORDER BY ENTRY_DATE)
> WHERE ROWNUM < 5
>
> Second SQL statement
> --------------------
> SELECT * FROM
> (SELECT PROCESSED_DATE
> FROM TRADES WHERE PROCESSED_STATUS = 0
> ORDER BY ENTRY_DATE)
> WHERE ROWNUM = 5
>
> ---------------
> Question:
>
> Why second SQL statement does not work?
>
> I would be thankful for the clarification.
>
> Thanks,
>
> Rao
> [EMAIL PROTECTED]
>
Rao,
rownums are computed on the fly. In the first case the first row is
returned, is assigned rownum 1, it matches the condition, so it is
displayed, and so forth until the fifth row which brings everything to
an end. In the second case, since you never display a row # 1, you can
never get a row # 5. To get row 5 out, you must first pull the 4 other
rows out, which you can do by writing (beware, it's beginning to be
subtle) :
SELECT PROCESSED_DATE
FROM (SELECT PROCESSED_DATE, ROWNUM NUM
FROM (SELECT PROCESSED_DATE
FROM TRADES
WHERE PROCESSED_STATUS = 0
ORDER BY ENTRY_DATE)
WHERE ROWNUM < 6)
WHERE NUM = 5
A word of explanation may be helpful. We must get out those ��%�# 4
rows before the one we are interested in. So we use what you have used
so far and which works, to limit our fetching rows to what is STRICTLY
necessary. However, we just want the last one in the list, so we add the
rownum (renamed num, since rownum is a reserved word, to be able to
referenced it at the outer level) and, since this is an embedded query,
it becomes an almost ordinary column at the outer level and we can
specify our screening condition.
I have never used them (never really felt the necessity), but it is not
impossible that the so-called analytical functions could not be used in
such a case. However, I am pretty confident that what is above must be
close to the most efficient you can get to.
Now an aspirin.
--
Regards,
Stephane Faroult
Oriole Corporation
Voice: +44 (0) 7050-696-269
Fax: +44 (0) 7050-696-449
Performance Tools & Free Scripts
--------------------------------------------------------------
http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs
--------------------------------------------------------------
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Stephane Faroult
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
