Re: Using ROWNUM with ORDER BY clause with a CASE statement

From: VC <boston103_at_hotmail.com>
Date: Wed, 05 Nov 2003 23:17:08 GMT
Message-ID: <Uxfqb.115990$e01.421542@attbi_s02>

Hello Brett,

Why not just:

select *
  from ( select t1.*, rownum rn

           from ([ your select with order by] )
          where rownum <= upper_limit ) t1

 where rn >= lower_limit;

Rgds.

"Brett Cramer" <bcramer0515_at_hotmail.com> wrote in message news:ddbaaaea.0311051229.e1002d0_at_posting.google.com...
> I know how to grab records 10 - 20 of a 100 row resultset using
> ROWNUM, but now I've complicated things a bit. I have a rule in the
> ORDER BY that brings any record that has "XXX" at the end of one
> particular field's value to appear first in the result set. The
> problem is, the ROWNUM for some of these records may be greater than
> the ROWNUM value I've specified. Let's say the record I want to
> appear happens to have a ROWNUM of 69, so it should appear at (along
> with other rows that qualify) at the top of the resultset in my 10 -
> 20 range, but since I am asking for ROWNUM BETWEEN 10 AND 20, the row
> with ROWNUM of 69 gets left out. How can I get this row to be
> included?
>
> Let me preface this with the fact that we are not to use stored
> procedures. I have to do this with bare-ass SQL. Here is the SQL
> that I have so far.
> Select * FROM ( Select SWIFT_BANKS.*, ROWNUM RN FROM SWIFT_BANKS
> WHERE SWIFT_ID LIKE 'HABBPKKA%'
> ORDER BY (CASE SUBSTR(SWIFT_ID,9,3) WHEN
> 'XXX' THEN 1 ELSE ASCII(SWIFT_ID) END) )
> WHERE RN BETWEEN 1 AND 21
>
>
> It returns 69 records of which I choose the first 20.
>
> If I run just this part:
>
> Select * FROM ( Select SWIFT_BANKS.*, ROWNUM RN FROM SWIFT_BANKS
> WHERE SWIFT_ID LIKE 'HABBPKKA%'
> ORDER BY (CASE SUBSTR(SWIFT_ID,9,3) WHEN
> 'XXX' THEN 1 ELSE ASCII(SWIFT_ID) END) )
>
> ...I will get the results I want as the XXX records appear first, I
> just get 69 rows, not 20. When I add the line "WHERE RN BETWEEN 1 AND
> 21" to the end, it negates my ORDER BY rule for the "XXX". How can I
> accomplish getting my range of rows AFTER the ORDER BY is enforced??
Received on Wed Nov 05 2003 - 17:17:08 CST