SQL (Oracle): Order by and Limit

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.

SQL (ORACLE): ORDER BY and LIMIT

Prior to 12.1, Oracle does not support the LIMIT or OFFSET keywords. If you want to retrieve rows N through M of a result set, you'd need something like:

SELECT a.*
  FROM (SELECT b.*,
               rownum b_rownum
          FROM (SELECT c.*
                  FROM some_table c
                 ORDER BY some_column) b
         WHERE rownum <= <<upper limit>>) a
 WHERE b_rownum >= <<lower limit>>

or using analytic functions:

SELECT a.*
  FROM (SELECT b.*,
               rank() over (order by some_column) rnk
          FROM some_table)
 WHERE rnk BETWEEN <<lower limit>> AND <<upper limit>>
 ORDER BY some_column

Either of these approaches will sort give you rows N through M of the sorted result.

In 12.1 and later, you can use the OFFSET and/or FETCH [FIRST | NEXT] operators:

SELECT *
  FROM some_table
 ORDER BY some_column
 OFFSET <<lower limit>> ROWS
  FETCH NEXT <<page size>> ROWS ONLY

How to limit the results to 1 row in Oracle SQL

In Oracle you need to do the ordering first and then select rownum. Thus, you need to nest the query which returns the sorted data and take the filtering WHERE clause outside.

SELECT * FROM
(
 SELECT customerNumber
 FROM ORDERS 
 GROUP BY customerNumber 
 ORDER BY count(orderNumber) DESC
) resultSet
WHERE ROWNUM=1;

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;

Limit Clause in Oracle 11g

In older versions of Oracle, you need a subquery:

select c.*
from (select c.*, row_number() over (order by c.points desc) as seqnum
      from customers c
     ) c
where seqnum = 2;

You will see examples that use rownum in the outer query:

select c.*
from (select c.*
      from customers c
     ) c
where rownum = 2;

However, that does not work, because rownum is incremented only when rows are placed in the result set. Window functions are the simplest solution in older versions of Oracle (new versions support fetch/offset).

limit' clause in Oracle SQL SQL command not properly ended

Generally, we use LIMIT in MYSQL database and Rownum in Oracle.

MySQL Syntax:

SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;

Oracle Syntax:

SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;

References:

https://www.w3schools.com/sql/sql_top.asp

SQL command not properly ended when using LIMIT

I have resolved the issue by using the below query.

SELECT * FROM Person where person_name='rahul' and rownum between 1 and 2 order by created_time desc;

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)

---

Related Topics