Oracle SELECT TOP 10 records

Question

I have a big problem with an SQL Statement in Oracle. I want to select the TOP 10 Records ordered by STORAGE_DB which aren't in a list from another select statement.

This one 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 I am adding

AND ROWNUM <= 10

ORDER BY STORAGE_GB DESC

I'm getting some kind of "random" Records. I think because the limit takes in place before the order.

Does someone have a good solution? The other problem: This query is really slow (10k+ records)

Answer 1

Try to put your current query in subquery as follows :

SELECT * FROM (

  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')

  ORDER BY STORAGE_GB DESC )

WHERE ROWNUM <= 10

In Oracle, rownum is used to the result after it is returned. You just have to filter the result after it has been returned, therefore the subquery will be required. 

You could also use the RANK() function to get Top-N results. For performance, you can use NOT EXISTS in place of NOT IN.