How to limit the results to 1 row in Oracle SQL [duplicate]

Question

I've this query which runs successfuly

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

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 

Here's what I've tried :

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

and

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

Answer 1

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;

Answer 2

You can combine grouping and window functions to accomplish this.

select customernumber, num_orders
from (
  SELECT customerNumber, 
         count(*) as num_orders,
         dense_rank() over (order by count(*) desc) as rnk
  from orders
  group by customerNumber
) t
where rnk = 1;

The difference to a simple "get me only one row" is that this will also return multiple customers that have the same number of orders. If you don't want that, replace dense_rank() with row_number()