Oracle SELECT TOP 10 records

Question

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  lt   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

User · Accepted Answer

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  lt   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

User · Answer

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

User · Answer

You get an apparently random set because ROWNUM is applied before the ORDER BY  So your query takes the first ten rows and sorts them 0 To select the top ten salaries you should use an analytic function in a subquery  then filter that    select   from        select empno               ename               sal               row number   over order by sal desc nulls last  rnm     from emp    where rnm lt  10

User · Answer

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

User · Answer

you may use this query for selecting top records in oracle  Rakesh B select   from User info where id  gt    select max id -10 from User info

User · Answer

try   SELECT   FROM users FETCH NEXT 10 ROWS ONLY

[sql] Oracle SELECT TOP 10 records

Examples related to sql

Examples related to oracle

Examples related to top-n