如何在Oracle SQL中将结果限制为1行 [英] How to limit the results to 1 row in Oracle SQL

问题描述

我已经成功运行了该查询

I've this query which runs successfuly

SELECT customerNumber
FROM ORDERS 
GROUP BY customerNumber 
ORDER BY count(orderNumber) DESC

但是当我尝试将返回的行数限制为1时，出现以下错误

But when I try to limit the number of rows returned to 1, I get the following error

ORA-00933: SQL command not properly ended 

这就是我尝试过的:

SELECT customerNumber
FROM ORDERS 
GROUP BY customerNumber 
ORDER BY count(orderNumber) DESC
fetch first 1 row only;

和

SELECT customerNumber
FROM ORDERS 
GROUP BY customerNumber 
ORDER BY count(orderNumber) DESC
WHERE ROWNUM=1;

推荐答案

在Oracle中，您需要先进行排序，然后选择rownum.因此，您需要嵌套返回排序数据的查询，并将过滤WHERE子句带到外部.

In Oracle you need to do the ordering first and then select rownum. Thus, you need to nest the query which returns the sorted data and take the filtering WHERE clause outside.

SELECT * FROM
(
 SELECT customerNumber
 FROM ORDERS 
 GROUP BY customerNumber 
 ORDER BY count(orderNumber) DESC
) resultSet
WHERE ROWNUM=1;

这篇关于如何在Oracle SQL中将结果限制为1行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！