Give Me The Last X Records Ordered by These Criteria

While writing a script to analyze application log, written into Oracle database, I found that it is fairly hard to get the last X records using rownum after the records are ordered based on certain criteria.

Trupti Rajparia says:

[...] one should be careful when using the Order By clause along with Rownum. When Order By is used with Rownum to restrict query results, it works only if Ordered By is the primary key of the table.

Since I am not a great fan of PL/SQL (clunky POS), I decided to use Perl instead to slice and dice the matching criteria.

1. Get the INDEXED_CRITERIA from Oracle

SELECT
   INDEXED_CRITERIA
FROM logTable
WHERE
   filter criteria
ORDER BY
   INDEXED_CRITERIA


2. Get the last X INDEXED_CRITERIA (easily processed by Perl with three standard functions)
   

push each INDEXED_CRITERIA into array tempA
pop the last X records from array tempA into array tempB.
produce CRITERIA_TEXT by joining tempB with ',' as separator


3. Get the data from Oracle
   

SELECT
   REQUIRED_FIELDS
FROM logTable
WHERE
   CRITERIA_TEXT are met
ORDER BY
   INDEXED_CRITERIA


4. Process the data returned.