Oracle Select Top 10 Records

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.

How to select 10 rows from column in Oracle

If you just want any 10 random rows, then just use ROWNUM:

SELECT * FROM table_name 
   WHERE ROWNUM <=10;

If you want 10 rows for a specific set of values that meet the condition, then add a filter predicate:

SELECT * FROM table_name 
WHERE identification number IN (1, 2, 3,4, 5, 6, 7, 8, 9, 10);

If there are duplicate values for identification number column, then again add ROWNUM to filter the rows, the rows would again be randomly selected:

SELECT * FROM table_name 
   WHERE identification number IN (1, 2, 3,4, 5, 6, 7, 8, 9, 10) 
   AND   ROWNUM <=10;

If you want 10 rows in a particular ORDER, then you must first sort them and then apply ROWNUM to limit the number of rows in the final output:

SELECT * FROM(
   SELECT * FROM table_name 
      WHERE identification number IN (1, 2, 3,4, 5, 6, 7, 8, 9, 10) 
    ORDER BY identification number
             )
  WHERE ROWNUM <=10;

You might also be interested in the new Top-n row limiting feature introduced in Oracle 12c.

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

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)

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 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 do I do top 1 in Oracle?

If you want just a first selected row, you can:

select fname from MyTbl where rownum = 1

You can also use analytic functions to order and take the top x:

select max(fname) over (rank() order by some_factor) from MyTbl

TOP 10 rows in ORACLE when query contains GROUP BY clause

When selecting using rownum in Oracle, you need to use a subquery:

select p.*
from (select dim_product.PRODUCT_NAME, sum(units_sold * (revenue - cost)) 
      from FT_SALES INNER JOIN
           dim_product 
           ON ft_sales.prod_dwh_id = dim_product.product_dwh_id
      group by dim_product.PRODUCT_NAME
      order by 2 desc
     ) p
where rownum <= 10

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

---

Related Topics