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;

fetch first 1 row only; needs Oracle 12 - which version are you using? Are you trying to find the customer with the most orders? — user330315
– user330315, Commented Dec 19, 2016 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
– Satwik, Commented Dec 19, 2016 at 10:57

Radu Gheorghiu · Accepted Answer · 2016-12-19 10:51:08Z

13

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, 2016 at 10:51

Radu Gheorghiu

20.6k16 gold badges78 silver badges113 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Satwik Over a year ago

Thanks! It worked without using the resultSet as well. Why is that required?

Radu Gheorghiu Over a year ago

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

score 1 · Accepted Answer · 2016-12-19 11:52:19Z

1

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

edited Dec 19, 2016 at 11:52

answered Dec 19, 2016 at 10:52

user330315

Comments

ScaisEdge · Accepted Answer · 2016-12-19 10:52:03Z

0

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;

answered Dec 19, 2016 at 10:52

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Linked

Related