oracle sql how to fetch 1 line only from return result

Question

how do i improved this code.

select code,max(total) from
(select code,count(*) from table_1 group by code)

The above code,not working because I try to do MAX function onto the result set from query,but failed.

Look at http://stackoverflow.com/questions/470542/how-do-i-limit-the-number-of-rows-returned-by-an-oracle-query and http://www.club-oracle.com/forums/limit-clause-for-oracle-sql-t637/ — Jan S, Nov 25 '11 at 08:17
what exactly is the goal ? Is `total` a column from `table_1` or is it the value of `count(*)` ? — Yahia, Nov 25 '11 at 08:20

score 2 · Answer 1 · answered Nov 25 '11 at 08:20

If you only want the number, then you can use this:

select max(total) 
from (
     select code,
            count(*) as total -- you forgot the column alias here
     from table_1 
     group by code
)

If you want the code and the number, use the following:

with count_result as (
     select code,
            count(*) as total
     from table_1 
     group by code
) 
select code, 
       total
from count_result
where total = (select max(total) from count_result);

oracle sql how to fetch 1 line only from return result

1 Answers1