Using ROWNUM with ORDER BY clause with a CASE statement

From: Brett Cramer <bcramer0515_at_hotmail.com>
Date: 5 Nov 2003 12:29:55 -0800
Message-ID: <ddbaaaea.0311051229.e1002d0@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 - 14:29:55 CST