Search code examples
sqloracle-databasetop-n

Oracle SELECT TOP 10 records


I have an 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 an other 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 has an good solution? The other problem: This query is realy slow (10k+ records)


Solution

  • You'll need to put your current query in subquery as below :

    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
    

    Oracle applies rownum to the result after it has been returned.
    You need to filter the result after it has been returned, so a subquery is required. You can also use RANK() function to get Top-N results.

    For performance try using NOT EXISTS in place of NOT IN. See this for more.