rownum doesnt work sql

Question

im trying to get the most row appears in the table, when i use in rownum i get error: "SQL command not properly ended", there is something else that i can do?

 select src_customer ,count(sending.src_customer) as bla
    from sending
    group by sending.src_customer
    order by bla desc
    where rownum=1;

rownums are assigned before the order by clause. Use Top or select * from ( select src_customer ,count(sending.src_customer) as bla from sending group by sending.src_customer order by bla desc ) where rownum=1; — bassxzero, May 21 '15 at 23:09

score 2 · Answer 1 · answered May 22 '15 at 00:51

Presumably, you want the largest row. In Oracle 12, you can use the ANSI standard FETCH clause:

select src_customer, count(s.src_customer) as bla
from sending s
group by s.src_customer
order by bla desc
fetch first 1 row only;

In earlier versions, you need a subquery:

select *
from (select src_customer, count(s.src_customer) as bla
      from sending s
      group by s.src_customer
      order by bla desc
     ) s
where rownum = 1;

score 1 · Answer 2 · answered May 21 '15 at 23:06

Your WHERE clause should be after the FROM clause.

See Oracle documentation for more details

So, your query should be like

select src_customer ,count(sending.src_customer) as bla
    from sending
    where rownum=1
    group by sending.src_customer
    order by bla desc
    ;

rownum doesnt work sql

2 Answers2