如何在 Oracle 中选择前100行？

我的要求是得到每个客户的最新订单，然后得到前100名的记录。

我编写了一个如下查询来获取每个客户的最新订单。内部查询工作正常。但我不知道如何根据结果得到第一个100分。

    SELECT * FROM (
SELECT id, client_id, ROW_NUMBER() OVER(PARTITION BY client_id ORDER BY create_time DESC) rn
FROM order
) WHERE rn=1

有什么想法吗? 谢谢。

463924 次浏览
小开

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.

小开

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

小开
最佳答案

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)
小开

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

小开

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;
小开

To select top n rows updated recently

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

提交答案