CODEWITHSUNDEEP
sqloracle-database
17tucs146 Ragul
17tucs146 Ragul

Reputation: 13

SQL selecting maximum month in oracle

Hey there guys I had a bit of a problem with the current project I'm working on. The schema is

THE SCHEMA

The question is Display month and the number of books when the maximum number of books were rented

The output is !THE OUTPUT

The code I was able to wrote got me the maximum number of books select max(count(book_id)) from bookrental.book_rental group by TO_CHAR(rental_start_date,'MON');

Also when I use

 select TO_CHAR(rental_start_date,'MON') 
 from bookrental.book_rental group by(TO_CHAR(rental_start_date,'MON'))
 having count(book_id)= (select
            max(count(book_id)) 
           from bookrental.book_rental group by TO_CHAR(rental_start_date,'MON'));

Now I need to display the month and the number of books in a single table. I can't manage to join them both.

Can anyone help me out?

Upvotes: 1

Views: 74

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You are much better off using window functions. The equivalent to your attempt would be:

select m.*
from (select TO_CHAR(rental_start_date, 'MON'), count(*) as cnt,
             rank() over (order by count(*) desc) as seqnum
      from bookrental.book_rental br
      group by TO_CHAR(rental_start_date, 'MON')
     ) m
where seqnum = 1;

But, I don't think this is the best answer for two reasons:

  1. "month" in this context probably refers to calendar month. The above code counts all book sales in January, say, regardless of eyar.
  2. The question is in the singular ("Display month") which suggests that it doesn't want ties.

To fix the first problem, I would use trunc(). To fix the second, I would use row_number():

select m.*
from (select trunc(rental_start_date, 'MON'), count(*) as cnt,
             row_number() over (order by count(*) desc) as seqnum
      from bookrental.book_rental br
      group by trunc(rental_start_date, 'MON')
     ) m
where seqnum = 1;

Finally, this version can be simplified using fetch first:

select trunc(rental_start_date, 'MON'), count(*) as cnt,
             row_number() over (order by count(*) desc) as seqnum
from bookrental.book_rental br
group by trunc(rental_start_date, 'MON')
order by count(*) desc
fetch first 1 row only;

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133400

seems you miss the count(book_id)

 select TO_CHAR(rental_start_date,'MON') , count(book_id)
 from bookrental.book_rental 
 group by(TO_CHAR(rental_start_date,'MON'))
 having count(book_id)= (
        select
        max(count(book_id)) 
       from bookrental.book_rental 
        group by TO_CHAR(rental_start_date,'MON'));

Upvotes: 1

Related Questions