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

User · Answer

To select top n rows updated recently SELECT    FROM      SELECT       FROM table     ORDER BY UpdateDateTime DESC   WHERE ROWNUM  lt  101

User · Answer

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

User · Answer

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

User · Answer

First 10 customers inserted into db  table customers    select   from customers where customer id  lt    select  min customer id  10 from customers   Last 10 customers inserted into db  table customers    select   from customers where customer id  gt    select  max customer id -10 from customers    Hope this helps

User · Answer

As Moneer Kamal said  you can do that simply   SELECT id  client id FROM order  WHERE rownum  lt   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

User · Answer

you should use rownum in oracle to do what you seek  where rownum  lt   100   see also those answers to help you  limit in oracle  select top in oracle  select top in oracle 2

[sql] How to Select Top 100 rows in Oracle?

Examples related to sql

Examples related to oracle