Peebl Peebl - 1 month ago 5
SQL Question

How to select the highest value after a count() | Sql Oracle

This is my query:

SELECT f.name, COUNT(*) as num_books
from author f
JOIN book b on b.tittle = f.book
Group by f.name


Which gives me this table:

NAME NUM_BOOKS
-------------------------------------------------- ----------
Dyremann 2
Nam mann 1
Thomas 1
Asgeir 1
Tullemann 5
Plantemann 1
Beste forfatter 1
Fagmann 5
Lars 1
Hans 1
Svein Arne 1


How could I easly alter the query to only display the author with the highest amount of released books? (While keeping in mind I'm rather new to sql)

Answer

Oracle, and as far as I know - only Oracle, allows you to nest two aggregate functions.

SELECT max (f.name) keep (dense_rank last order by count (*)) as name
from author f
JOIN book b on b.tittle = f.book
Group by f.name

In order to get ALL top authors:

select   name
from    (SELECT f.name,rank () over (order by count(*) desc) as rnk
         from author f
         JOIN book b on b.tittle = f.book
         Group by f.name
         ) 
 where   rnk = 1

Since Oracle 12c:

SELECT f.name
from author f
JOIN book b on b.tittle = f.book
Group by f.name
order by count (*) desc
fetch first row /* with ties (optional, in order to get all top authors) */
Comments