CODEWITHSUNDEEP
sqloracle-database
user2321728
user2321728

Reputation: 1373

How to Select Top 100 rows in Oracle?

My requirement is to get each client's latest order, and then get top 100 records.

I wrote one query as below to get latest orders for each client. Internal query works fine. But I don't know how to get first 100 based on the results.

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

Any ideas? Thanks.

Upvotes: 105

Views: 614534

Answers (7)

Fetch Lives
Fetch Lives

Reputation: 1

First, to just get the first N rows in a table regardless of any ordering this will work:

SELECT *
FROM Order
WHERE ROWNUM <= N;

But say you want to get the last 50 orders?

SELECT *
FROM Order
WHERE ROWNUM <=N
ORDER BY Created_date DESC;

This would actually retrieve the first N rows then sort them by the Created_Date.

What is needed is to sort the records by date first then get the N rows

SELECT * 
FROM Order
ORDER BY Created_date DESC
FETCH FIRST N ROWS ONLY;

OR

SELECT *
FROM
(
  SELECT *
  FROM Order
  ORDER BY Created_Date DESC
)
WHERE ROWNUM <=N;

Upvotes: 0

Usama
Usama

Reputation: 944

As Moneer Kamal said, you can do that simply:

SELECT id, client_id FROM order 
WHERE rownum <= 100
ORDER BY create_time DESC;

Notice that the ordering is done after getting the 100 row. This might be useful for who does not want ordering.

Update:

To use order by with rownum you have to write something like this:

SELECT * from (SELECT id, client_id FROM order ORDER BY create_time DESC) WHERE rownum <= 100;

Upvotes: 16

Ele
Ele

Reputation: 11

To select top n rows updated recently

SELECT * 
FROM (
   SELECT * 
   FROM table 
   ORDER BY UpdateDateTime DESC
)
WHERE ROWNUM < 101;

Upvotes: 1

Lorenzo
Lorenzo

Reputation: 9

First 10 customers inserted into db (table customers):

select * from customers where customer_id <=
(select  min(customer_id)+10 from customers)

Last 10 customers inserted into db (table customers):

select * from customers where customer_id >=
(select  max(customer_id)-10 from customers)

Hope this helps....

Upvotes: 0

Frank Schmitt
Frank Schmitt

Reputation: 30845

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)

Upvotes: 107

Sarz
Sarz

Reputation: 1976

Try this:

   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) alias_name
WHERE rownum <= 100
ORDER BY rownum;

Or TOP:

SELECT TOP 2 * FROM Customers; //But not supported in Oracle

NOTE: I suppose that your internal query is fine. Please share your output of this.

Upvotes: -5

Moneer Kamal
Moneer Kamal

Reputation: 1877

you should use rownum in oracle to do what you seek

where rownum <= 100

see also those answers to help you

limit in oracle

select top in oracle

select top in oracle 2

Upvotes: 77

Related Questions