oracle - How to lists the maximum of counting with where syntax? -

for oracle,

i have 2 tables; first store , book whereas connected store id (pk fk). lists name of store has highest numbers of books. however, result showed every store in orders want highest.

    select store.store_name     store, book     store.store_id=book.book_storeid     group store.store_name     order count(book.book_storeid) desc; 

the result is

store: d e f b c

it should 'd'. should do? thank you.

try

select store_name (select store.store_name     store, book     store.store_id=book.book_storeid     group store.store_name     order count(book.book_storeid) desc) rownum = 1 

here sqlfiddle demo

btw, can use row_number() function

select store_name (select store.store_name,         row_number() over( order count(book.book_storeid)desc) rn     store join book on store.store_id=book.book_storeid group store.store_name) rn = 1; 

---

update if want see stores have max number of books can use rank instead of row_number:

select store_name (select store.store_name,         rank() over( order count(book.book_storeid)desc) rn     store join book on store.store_id=book.book_storeid group store.store_name) rn = 1;