entityMap|blocks|key|eks3o|text|试试这个：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|fpcrb|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;|code-block|syntax|javascript|7cke6|或顶部：|fepka|SELECT+TOP+2+*+FROM+Customers;+//But+not+supported+in+Oracle|js|8i961|注意：我想你的内部查询应该没问题。请分享你的输出。^0|0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|O|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|P|9|@]|A|@]|B|$F|G]]|$2|H|4|I|6|7|8|Q|9|@]|A|@]|B|$]]|$2|J|4|K|6|E|8|R|9|@]|A|@]|B|$F|L]]|$2|M|4|N|6|7|8|S|9|@]|A|@]|B|$]]]]

Try this:

<pre><code> 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;
</code></pre>

Or TOP:

<pre><code>SELECT TOP 2 * FROM Customers; //But not supported in Oracle
</code></pre>

NOTE: I suppose that your internal query is fine. Please share your output of this.

entityMap|0|type|LINK|mutability|MUTABLE|data|url|https://stackoverflow.com/questions/470542/how-do-i-limit-the-number-of-rows-returned-by-an-oracle-query-after-ordering|1|https://stackoverflow.com/questions/3451534/how-to-do-top-1-in-oracle|2|https://stackoverflow.com/questions/2498035/oracle-select-top-10-records|blocks|key|1lofp|text|你应该在oracle中使用rownum来做你想做的事情。|unstyled|depth|inlineStyleRanges|entityRanges|d30g0|where+rownum+<=+100|code-block|syntax|js|3isfl|另请参阅这些答案以帮助您|dpirm|oracle中的限制|offset|length|4vca8|顶部[在oracle]中选择|eatqo|顶部[在oracle+2]中选择|fioct|cd8ip^0|0|0|0|0|6|0|0|0|E|1|0|0|G|2|0|0^^$0|$1|$2|3|4|5|6|$7|8]]|9|$2|3|4|5|6|$7|A]]|B|$2|3|4|5|6|$7|C]]]|D|@$E|F|G|H|2|I|J|13|K|@]|L|@]|6|$]]|$E|M|G|N|2|O|J|14|K|@]|L|@]|6|$P|Q]]|$E|R|G|S|2|I|J|15|K|@]|L|@]|6|$]]|$E|T|G|U|2|I|J|16|K|@]|L|@$V|17|W|18|E|19]]|6|$]]|$E|X|G|Y|2|I|J|1A|K|@]|L|@$V|1B|W|1C|E|1D]]|6|$]]|$E|Z|G|10|2|I|J|1E|K|@]|L|@$V|1F|W|1G|E|1H]]|6|$]]|$E|11|G|-4|2|I|J|1I|K|@]|L|@]|6|$]]|$E|12|G|-4|2|I|J|1J|K|@]|L|@]|6|$]]]]

you should use rownum in oracle to do what you seek

<pre><code>where rownum &lt;= 100
</code></pre>

see also those answers to help you

<a href="https://stackoverflow.com/questions/470542/how-do-i-limit-the-number-of-rows-returned-by-an-oracle-query-after-ordering">limit in oracle</a>

<a href="https://stackoverflow.com/questions/3451534/how-to-do-top-1-in-oracle">select top in oracle</a>

<a href="https://stackoverflow.com/questions/2498035/oracle-select-top-10-records">select top in oracle 2</a>

entityMap|0|type|LINK|mutability|MUTABLE|data|url|https://oracle-base.com/articles/misc/top-n-queries|blocks|key|6h77b|text|假设create_time包含创建订单的时间，如果您希望100个客户端获得最新订单，您可以：|unstyled|depth|inlineStyleRanges|entityRanges|7u6fd|添加创建_最内部查询的时间|unordered-list-item|11bq5|属性对外部查询的结果进行排序。create_time+desc|offset|length|style|CODE|30rbl|添加一个最外层的查询，该查询使用以下命令过滤前100行ROWNUM|d0e3u|查询：|e79g4|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|code-block|syntax|javascript|7ekm|Oracle+12c更新|esjl6|在版本12.1中，Oracle引入了Top["real"+Top-N+queries](https://oracle-base.com/articles/misc/top-n-queries)..。使用新的|UNDERLINE|14uih|FETCH+FIRST...语法，您还可以使用：|2p7fa|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)^0|0|0|F|G|0|R|6|0|0|0|0|M|K|M|K|0|0|0|E|0^^$0|$1|$2|3|4|5|6|$7|8]]]|9|@$A|B|C|D|2|E|F|19|G|@]|H|@]|6|$]]|$A|I|C|J|2|K|F|1A|G|@]|H|@]|6|$]]|$A|L|C|M|2|K|F|1B|G|@$N|1C|O|1D|P|Q]]|H|@]|6|$]]|$A|R|C|S|2|K|F|1E|G|@$N|1F|O|1G|P|Q]]|H|@]|6|$]]|$A|T|C|U|2|E|F|1H|G|@]|H|@]|6|$]]|$A|V|C|W|2|X|F|1I|G|@]|H|@]|6|$Y|Z]]|$A|10|C|11|2|E|F|1J|G|@]|H|@]|6|$]]|$A|12|C|13|2|E|F|1K|G|@$N|1L|O|1M|P|14]]|H|@$N|1N|O|1O|A|1P]]|6|$]]|$A|15|C|16|2|E|F|1Q|G|@$N|1R|O|1S|P|Q]]|H|@]|6|$]]|$A|17|C|18|2|X|F|1T|G|@]|H|@]|6|$Y|Z]]]]

Assuming that create_time contains the time the order was created, and you want the 100 clients with the latest orders, you can:

<ul>
<li>add the create_time in your innermost query</li>
<li>order the results of your outer query by the <code>create_time desc</code></li>
<li>add an outermost query that filters the first 100 rows using <code>ROWNUM</code></li>
</ul>

Query: 

<pre><code> 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
</code></pre>

UPDATE for Oracle 12c

With release 12.1, Oracle introduced <a href="https://oracle-base.com/articles/misc/top-n-queries" rel="noreferrer">"real" Top-N queries</a>. Using the new <code>FETCH FIRST...</code> syntax, you can also use:

<pre><code> 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)
</code></pre>

entityMap|blocks|key|efj7l|text|插入数据库的前10个客户(表客户)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|o99a|select+*+from+customers+where+customer_id+<=
(select++min(customer_id)%2B10+from+customers)

Last+10+customers+inserted+into+db+(table+customers):

select+*+from+customers+where+customer_id+>=
(select++max(customer_id)-10+from+customers)|code-block|syntax|javascript|a0i0d|希望这能帮上忙..。^0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|J|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|K|9|@]|A|@]|B|$F|G]]|$2|H|4|I|6|7|8|L|9|@]|A|@]|B|$]]]]

First 10 customers inserted into db (table customers):

<pre><code>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)
</code></pre>

Hope this helps....

entityMap|blocks|key|9ssuq|text|正如Moneer+Kamal所说，你可以简单地做到这一点：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|aiimu|SELECT+id,+client_id+FROM+order+
WHERE+rownum+<=+100
ORDER+BY+create_time+DESC;|code-block|syntax|javascript|8j10l|请注意，排序已经完成。之后得到100行。这对于不想排序的人可能很有用。^0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|J|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|K|9|@]|A|@]|B|$F|G]]|$2|H|4|I|6|7|8|L|9|@]|A|@]|B|$]]]]

As Moneer Kamal said, you can do that simply:

<pre><code>SELECT id, client_id FROM order 
WHERE rownum &lt;= 100
ORDER BY create_time DESC;
</code></pre>

Notice that the ordering is done after getting the 100 row. This might be useful for who does not want ordering.

entityMap|blocks|key|7qrfo|text|选择最近更新的前n行|type|unstyled|depth|inlineStyleRanges|entityRanges|data|89ooa|SELECT+*+
FROM+(
+++SELECT+*+
+++FROM+table+
+++ORDER+BY+UpdateDateTime+DESC
)
WHERE+ROWNUM+<+101;|code-block|syntax|javascript^0|0^^$0|$]|1|@$2|3|4|5|6|7|8|H|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|I|9|@]|A|@]|B|$F|G]]]]

To select top n rows updated recently
<pre><code>SELECT * 
FROM (
 SELECT * 
 FROM table 
 ORDER BY UpdateDateTime DESC
)
WHERE ROWNUM &lt; 101;
</code></pre>

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.

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

Any ideas? Thanks.

How to Select Top 100 rows in Oracle?

数据库

 我的要求是获取每个客户的最新订单，然后获取前100条记录。 我编写了一个查询，如下所示，以获取每个客户的最新订单。内部查询工作正常。但我不知道如何根据结果获得前100名。 SELECT * FROM (      SELECT id, client_id, ROW_NUMBER() OVER(PARTITION BY...

问如何在Oracle中选择前100行？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在Oracle中选择前100行？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在Oracle中选择前100行？
EN