How to limit the results to 1 row in Oracle SQL

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;

`fetch first 1 row only;` needs Oracle 12 - which version are you using? Are you trying to find the customer with the most orders? — a_horse_with_no_name, Dec 19 '16 at 10:49
It's an online editor so I don't know the version, but I'm trying to find customer with most orders. — Satwik, Dec 19 '16 at 10:57

score 10 · Accepted Answer · answered Dec 19 '16 at 10:51

10

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;

answered Dec 19 '16 at 10:51

Radu Gheorghiu

18,331
15
65
96

Thanks! It worked without using the `resultSet` as well. Why is that required? – Satwik Dec 19 '16 at 10:55
@Satwik Oh, it's something that in SQL Server is required. I added it by reflex, since I haven't worked with Oracle for some time now. For this query you can just take it out, if you're using Oracle. [More info on why I added the alias to the result set](http://dba.stackexchange.com/questions/16516/subqueries-aliases-same-as-main-queries-aliases). – Radu Gheorghiu Dec 19 '16 at 10:56

a_horse_with_no_name · Answer 2 · 2016-12-19T11:52:19.103

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()

score 0 · Answer 3 · answered Dec 19 '16 at 10:52

the where condition must be placed before the order by (but seem that you need the first row after the sorted is done )

so you should use a select this wya

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

How to limit the results to 1 row in Oracle SQL

3 Answers3