How to Select Top 100 rows in Oracle?

Question

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.

Answer 1

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) 

Answer 2

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