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.

Answer 2

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 control the display of the data after it is selected. Hence, the main query will bring back the results for you that you can filter out later on. That's why the subquery will be necessary.

The method of ranking might also be helpful in this case to get Top-N results besides these ones include using the NOT EXISTS condition instead of NOT IN.