Oracle Top N Ordered Rows

How to select top 1 and ordered by date in Oracle SQL?

... where rownum = 1 order by trans_date desc

This selects one record arbitrarily chosen (where rownum = 1) and then sorts this one record (order by trans_date desc).

As shown by Ivan you can use a subquery where you order the records and then keep the first record with where rownum = 1in the outer query. This, however, is extremely Oracle-specific and violates the SQL standard where a subquery result is considered unordered (i.e. the order by clause can be ignored by the DBMS).

So better go with the standard solution. As of Oracle 12c:

select * 
from table_name 
order by trans_date desc
fetch first 1 row only;

In older versions:

select *
from
(
  select t.*, row_number() over (order by trans_date desc) as rn
  from table_name t
)
where rn = 1;

How to select top five or 'N' rows in Oracle 11g

You'll need to use DISTINCT before you select the "top 5":

SELECT * FROM 
(SELECT DISTINCT ani_digit, ani_business_line FROM cta_tq_matrix_exp) A
WHERE rownum <= 5

Oracle TOP N ordered rows

Your inference that Oracle must return all rows in the subquery before filtering out the first N is wrong. It will start fetching rows from the subquery, and stop when it has returned N rows.

Having said that, it may be that Oracle needs to select all rows from the table and sort them before it can start returning them. But if there were an index on the column being used in the ORDER BY clause, it might not.

Oracle is in the same position as any other DBMS: if you have a large table with no index on the column you are ordering by, how can it possibly know which rows are the top N without first getting all the rows and sorting them?

How do I limit the number of rows returned by an Oracle query after ordering?

You can use a subquery for this like

select *
from  
( select * 
  from emp 
  order by sal desc ) 
where ROWNUM <= 5;

Have also a look at the topic On ROWNUM and limiting results at Oracle/AskTom for more information.

Update:
To limit the result with both lower and upper bounds things get a bit more bloated with

select * from 
( select a.*, ROWNUM rnum from 
  ( <your_query_goes_here, with order by> ) a 
  where ROWNUM <= :MAX_ROW_TO_FETCH )
where rnum  >= :MIN_ROW_TO_FETCH;

(Copied from specified AskTom-article)

Update 2:
Starting with Oracle 12c (12.1) there is a syntax available to limit rows or start at offsets.

SELECT * 
FROM   sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

See this answer for more examples. Thanks to Krumia for the hint.

How to Select Top 100 rows in Oracle?

Assuming that create_time contains the time the order was created, and you want the 100 clients with the latest orders, you can:

• add the create_time in your innermost query
• order the results of your outer query by the create_time desc
• add an outermost query that filters the first 100 rows using ROWNUM

Query:

  SELECT * FROM (
     SELECT * FROM (
        SELECT 
          id, 
          client_id, 
          create_time,
          ROW_NUMBER() OVER(PARTITION BY client_id ORDER BY create_time DESC) rn 
        FROM order
      ) 
      WHERE rn=1
      ORDER BY create_time desc
  ) WHERE rownum <= 100

UPDATE for Oracle 12c

With release 12.1, Oracle introduced "real" Top-N queries. Using the new FETCH FIRST... syntax, you can also use:

  SELECT * FROM (
    SELECT 
      id, 
      client_id, 
      create_time,
      ROW_NUMBER() OVER(PARTITION BY client_id ORDER BY create_time DESC) rn 
    FROM order
  ) 
  WHERE rn = 1
  ORDER BY create_time desc
  FETCH FIRST 100 ROWS ONLY)

Selecting top n elements of a group in Oracle

Is this what you are after?

My test set-up:

SQL> alter session set nls_date_format = 'DD-Mon-YYYY HH24:Mi:SS';

Session altered.

SQL> drop table so_test;

Table dropped.

SQL> create table so_test  (
  2    n varchar2(32)
  3  , v varchar2(32)
  4  , t date );

Table created.

SQL> 
SQL> insert into so_test values ( 'X' , 'Test1', to_date('01-Jan-2011 12:00:00','DD-Mon-YYYY HH24:Mi:SS') );

1 row created.

SQL> insert into so_test values ( 'X' , 'Test2', to_date('01-Jan-2011 13:00:00','DD-Mon-YYYY HH24:Mi:SS') );

1 row created.

SQL> insert into so_test values ( 'X' , 'Test3', to_date('01-Jan-2011 14:00:00','DD-Mon-YYYY HH24:Mi:SS') );

1 row created.

SQL> insert into so_test values ( 'Y' , 'Test5', to_date('02-Jan-2011 12:00:00','DD-Mon-YYYY HH24:Mi:SS') );

1 row created.

SQL> insert into so_test values ( 'Y' , 'Test6', to_date('03-Jan-2011 12:00:00','DD-Mon-YYYY HH24:Mi:SS') );

1 row created.

SQL> insert into so_test values ( 'Y' , 'Test7', to_date('04-Jan-2011 12:00:00','DD-Mon-YYYY HH24:Mi:SS') );

1 row created.

SQL> 

Here is the query:

SQL> select n,v,t from (
  2  select n, v , t , rank() over ( partition by n order by t desc) r
  3  from so_test
  4  ) where r <= 2;

N                V                T
-------------------------------- -------------------------------- --------------------
X                Test3                01-Jan-2011 14:00:00
X                Test2                01-Jan-2011 13:00:00
Y                Test7                04-Jan-2011 12:00:00
Y                Test6                03-Jan-2011 12:00:00

SQL> 

Oracle SELECT TOP 10 records

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.

Oracle select top N rows based on Values

SELECT Marks FROM (
  SELECT Marks, DENSE_RANK() OVER (ORDER BY Marks DESC) AS MarksRank
  FROM yourtable
) WHERE MarksRank <= 4

Oracle: I need to select n rows from every k rows of a table

Assuming you are ordering them 1 - 10000 using some logic and want to output only rows 1-100,2001-2100,4001-4100,etc then you can use the ROWNUM pseudocolumn:

SELECT *
FROM   (
  SELECT t.*,
         ROWNUM AS rn            -- Secondly, assign a row number to the ordered rows
  FROM   (
    SELECT *
    FROM   your_table
    ORDER BY your_condition      -- First, order the data
  ) t
)
WHERE MOD( rn - 1, 2000 ) < 100; -- Finally, filter the top 100 per 2000.

Or you could use the ROW_NUMBER() analytic function:

SELECT *
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER ( ORDER BY your_condition ) AS rn
  FROM   your_table
)
WHERE  MOD( rn - 1, 2000 ) < 100;

Is it possible to increase the set of sample data exponentially. Like 1k, 2k, 4k,8k....and then fetch some rows from these.

Replace the WHERE clause with:

WHERE rn - POWER(
             2,
             TRUNC( CAST( LOG( 2, CEIL( rn / 1000 ) ) AS NUMBER(20,4) ) )
           ) * 1000 + 1000 <= 100

---

Related Topics