Re: Why this SQL does not work?

From: Stephane Faroult <sfaroult_at_oriole.com>
Date: Wed, 11 Jul 2001 14:28:24 -0700
Message-ID: <F001.00348107.20010711143700@fatcity.com>

"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
> Maheswara.Rao_at_Sungardp3.com
>

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: sfaroult_at_oriole.com

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: ListGuru_at_fatcity.com (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).

Received on Wed Jul 11 2001 - 16:28:24 CDT