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.

Just to clarify - do you want the first 100 for each client, or the first 100 clients? — Frank Schmitt, Nov 20 '14 at 08:21

Frank Schmitt · Accepted Answer · 2017-11-17T12:12:35.583

77

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)

edited Nov 17 '17 at 12:12

answered Nov 20 '14 at 08:29

Frank Schmitt

27,865
9
65
100

Actually I am confused, if I don't put another SELECT on top of my first query, but write ROWNUM<101 next to rn=1. Why the two results are different? – user2321728 Nov 20 '14 at 08:51
`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 AND rownum<=100 ORDER BY create_time desc` – user2321728 Nov 20 '14 at 08:52
1

Because in that case, you aren't sorting your rows by the create_time - your result set is randomly ordered, and you just take the first 100; that's essentially a random sample. – Frank Schmitt Nov 20 '14 at 08:53
It just gets top random 100 orders whose rn=1, right? – user2321728 Nov 20 '14 at 09:00
Yes. _this_comment_was_too_short_ – Frank Schmitt Nov 20 '14 at 09:43
@user2321728 No. The query that confuses you gets 100 random orders (since row numbers are effectively random) and *then* filters those 100 where `rn = 1`. – jpmc26 Jan 23 '18 at 01:16
Which version of Oracle are you running? `select * from v$version` – Wallace Kelly May 06 '18 at 00:09
1

Guys ... the 12.1 version is terrible and the prior one is outrageous! Do the people at Oracle not see how easy this is in SQL Server, MySQL, and PostGreSQL? – micahhoover May 03 '19 at 12:28
@micahhoover never have I thought that doing TOP x is not easy in different environment. – encryptoferia Jun 05 '20 at 08:23
upvoted for the 12C update – Mark Miller Feb 15 '21 at 17:23

score 52 · Answer 2 · edited May 23 '17 at 11:55

52

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

edited May 23 '17 at 11:55

Community

1
1

answered Nov 20 '14 at 08:21

Moneer Kamal

1,497
12
21

There are two ways to do this.`SELECT * FROM(SELECT * FROM ( SELECT id, client_id, ROW_NUMBER() OVER(PARTITION BY client_id ORDER BY create_time DESC) rn FROM order ) WHERE rn=1) WHERE ROWNUM < 101`Or `SELECT * FROM ( SELECT id, client_id, ROW_NUMBER() OVER(PARTITION BY client_id ORDER BY create_time DESC) rn FROM order ) WHERE rn=1 AND ROWNUM<101` I test these two, the results are different. – user2321728 Nov 20 '14 at 08:26

Usama · Answer 3 · 2019-07-01T12:15:21.343

7

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.

edited Jul 01 '19 at 12:15

answered Jul 01 '19 at 12:08

Usama

653
6
19

score 1 · Answer 4 · edited Jan 10 '19 at 12:43

1

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

edited Jan 10 '19 at 12:43

Phil3992

1,043
6
22
42

answered Jun 16 '16 at 10:09

Lorenzo

11
1

Your queries assume that there are no gaps in customer_id. If that assumption is wrong (because rows were deleted, because the sequence used to populate the PK uses caching / an increment greater than 1 / ...), it will return fewer rows than requested. – Frank Schmitt Nov 10 '16 at 20:05

score 1 · Answer 5 · edited Feb 25 '21 at 21:28

1

To select top n rows updated recently

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

edited Feb 25 '21 at 21:28

pringi

1,369
3
19
26

answered Feb 25 '21 at 14:47

Ele

11
1

Sarz · Answer 6 · 2014-11-20T09:29:27.163

-5

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.

edited Nov 20 '14 at 09:29

answered Nov 20 '14 at 08:11

Sarz

1,951
3
20
41

1

It gave error like "ORA-00923: FROM keyword not found where expected." I don't think TOP works. – user2321728 Nov 20 '14 at 08:19
4

Oracle doesn't support TOP – Frank Schmitt Nov 20 '14 at 08:20
Sorry but i dont have oracle installed yet in my system it is basic SQL command, thats why i ask for the output – Sarz Nov 20 '14 at 08:20
Edited try this for oracle – Sarz Nov 20 '14 at 08:22

How to Select Top 100 rows in Oracle?

6 Answers6

Linked