CODEWITHSUNDEEP
sqloracle-databaseselectmax
cistysok
cistysok

Reputation: 49

ORACLE SQL select max(count()) to year

I have database of library and i am trying to assign most borrowed title to each year like

2015 - The Great Gatsby
2014 - Da vinci code
2013 - Harry Potter
....

I've tried this but i am not sure about it

select to_char(borrow_date,'YYYY'),title_name
from k_title
join k_book
using(title_id)
join k_rent_books
using(book_id)
group by to_char(borrow_date,'YYYY'),title_name
having count(title_id) = (
select max(cnt) FROM(select count(title_name) as cnt
from k_title
join k_book
using(title_id)
join k_rent_books
using(book_id)
group by title_id,title_name,to_char(borrow_date,'YYYY')));

I've got only 3 results

2016 - Shogun
2006 - The Revolt of Mamie Stover
1996 - The Great Gatsby

I will be happy for any help :)

Upvotes: 1

Views: 182

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270011

Oracle has the nice capability to get the first or last value in an aggregation (as opposed to the min() or max()). This requires using something called keep.

So, the way to express what you want to do is:

select yyyy,
       max(title_name) keep (dense_rank first order by cnt desc) as title_name
from (select to_char(borrow_date, 'YYYY') as yyyy,
             title_name, count(*) as cnt
      from k_title t join
           k_book b
           using (title_id) join
           k_rent_books
           using (book_id)
      group by to_char(borrow_date, 'YYYY'), title_name
    ) yt
group by yyyy;

Your query is returning the year/title combinations that have the overall maximum count over all years, not the maximum per year.

Upvotes: 1

Related Questions