97

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.

Sarz
  • 1,970
  • 4
  • 23
  • 43
user2321728
  • 1,293
  • 1
  • 12
  • 17

6 Answers6

98

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)
Frank Schmitt
  • 30,195
  • 12
  • 73
  • 107
  • 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
74

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

Community
  • 1
  • 1
Moneer Kamal
  • 1,837
  • 16
  • 25
  • 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
15

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;
Usama
  • 884
  • 10
  • 24
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....

Phil3992
  • 1,059
  • 6
  • 21
  • 45
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
1

To select top n rows updated recently

SELECT * 
FROM (
   SELECT * 
   FROM table 
   ORDER BY UpdateDateTime DESC
)
WHERE ROWNUM < 101;
pringi
  • 3,987
  • 5
  • 35
  • 45
Ele
  • 11
  • 1
-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.

Sarz
  • 1,970
  • 4
  • 23
  • 43