Oracle SELECT TOP 10 records [duplicate]

Question

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.

If you are using Oracle 12c, use:

FETCH NEXT N ROWS ONLY

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
FETCH NEXT 10 ROWS ONLY

More info: http://docs.oracle.com/javadb/10.5.3.0/ref/rrefsqljoffsetfetch.html

try

SELECT * FROM users FETCH NEXT 10 ROWS ONLY;

With regards to the poor performance there are any number of things it could be, and it really ought to be a separate question. However, there is one obvious thing that could be a problem:

WHERE TO_CHAR(HISTORY_DATE, 'DD.MM.YYYY') = '06.02.2009') 

If HISTORY_DATE really is a date column and if it has an index then this rewrite will perform better:

WHERE HISTORY_DATE = TO_DATE ('06.02.2009', 'DD.MM.YYYY')  

This is because a datatype conversion disables the use of a B-Tree index.