How to select Oracle TOP 10 records

1142720 pts.
Tags:
Oracle
SQL statements
We have a huge problem with our SQL statement in Oracle. We're trying to select the TOP 10 records that are ordered by STORAGE_DB (which aren't in a list from another SELECT statement). This code works fine for all records:
SELECT DISTINCT 
  APP_ID, 
  NAME, 
  STORAGE_GB, 
  HISTORY_CREATED, 
  TO_CHAR(HISTORY_DATE, 'DD.MM.YYYY') AS HISTORY_DATE  
  FROM HISTORY WHERE 
      STORAGE_GB IS NOT NULL AND 
        APP_ID NOT IN (SELECT APP_ID
                       FROM HISTORY
                        WHERE TO_CHAR(HISTORY_DATE, 'DD.MM.YYYY') = '06.02.2009')
But when we add this:
AND ROWNUM <= 10
ORDER BY STORAGE_GB DESC
Then we get some "random" records. We think it's because the limit takes in place before the order. Would anyone have an idea on what to do?
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

You’re right, the ordering is applied after applying the WHERE conditions (which includes the limiting one).

This is what you need to do:

SELECT *
FROM
( <YOUR CURRENT QUERY>
ORDER BY storage_db DESC)
WHERE rownum <= 10;

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: