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 stackoverflow.com/questions/470542/… and club-oracle.com/forums/limit-clause-for-oracle-sql-t637 — Jan S
– Jan S, Commented Nov 25, 2011 at 8:17
what exactly is the goal ? Is total a column from table_1 or is it the value of count(*) ? — Yahia
– Yahia, Commented Nov 25, 2011 at 8:20

user330315 · Accepted Answer · 2011-11-25 08:20:05Z

2

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

answered Nov 25, 2011 at 8:20

user330315

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

Collectives™ on Stack Overflow

oracle sql how to fetch 1 line only from return result

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related