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

Padmarag · Accepted Answer · 2010-03-23T06:53:25.413

248

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.

edited Mar 23 '10 at 06:53

answered Mar 23 '10 at 06:46

Padmarag

7,067
1
25
29

NOT EXISTS is not working in this scenario (invalid relational operator) APP_ID NOT EXISTS (SELEC...) – opHASnoNAME Mar 23 '10 at 08:22
3

Some may say that this is apt to turn people off to Oracle. – MrBoJangles May 02 '18 at 16:33
2

Check the `FETCH NEXT N ROWS ONLY` answer below. – Mohnish Sep 28 '18 at 17:51
@Padmarag : When is a rownum apply in a query like this one - Select * from SomeTable where someColumn = '123' and rownum <=3. Is it after selecting the results from [Select * from SomeTable where someColumn = '123' ] – Farhan stands with Palestine Dec 03 '19 at 09:18
try to use: select * from (select * from history order by storage_gb desc) where rownum < 10; --(modify inner query to fetch distinct records) – Pavn Jan 06 '22 at 16:46

Volpato · Answer 2 · 2018-07-11T19:19:16.167

128

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

edited Jul 11 '18 at 19:19

answered Aug 03 '16 at 11:02

Volpato

1,543
1
10
10

9

this is gold compared to other answer – aswzen Jul 04 '18 at 07:18
If someone could say why it's better, that would be a good comment/edit. – Chris Jan 04 '23 at 19:46
If using simple query like `SELECT * FROM TABLE1 WHERE ROWNUM<=10` and `SELECT * FROM TABLE1 FETCH NEXT 10 ROWS ONLY`, `FETCH NEXT` seems like more costly since it will do this predicate `ROW_NUMBER() OVER ( ORDER BY NULL )<=10)` if check from the Explain Plan. – sanme98 Jun 20 '23 at 04:47

score 55 · Answer 3 · edited Oct 20 '18 at 07:42

55

try

SELECT * FROM users FETCH NEXT 10 ROWS ONLY;

edited Oct 20 '18 at 07:42

Barbaros Özhan

59,113
10
31
55

answered Jun 22 '17 at 12:31

Shaaban

577
4
3

APC · Answer 4 · 2013-02-15T09:37:34.313

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.

score 15 · Answer 5 · edited Oct 10 '17 at 06:25

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<=10

score -2 · Answer 6 · answered Jan 06 '21 at 16:17

-2

you may use this query for selecting top records in oracle. Rakesh B

select * from User_info where id >= (select max(id)-10 from User_info);

answered Jan 06 '21 at 16:17

Rakesh Bollu

1
2

Hey Rakesh! I'm not sure this answer fully addresses the question posed. Check out some of the other responses that provide more detail and are well-formatted for some examples. – krfurlong Jan 06 '21 at 17:35

Oracle SELECT TOP 10 records

6 Answers6

Linked

Related