'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.

sqloracle


Solution 1:[1]

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

Solution 2:[2]

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;

Solution 3:[3]

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....

Solution 4:[4]

To select top n rows updated recently

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Community
Solution 2
Solution 3 Phil3992
Solution 4 pringi

Related Questions