CASE expressions with MAX aggregate functions Orac

问题:

Using Oracle, I have selected the title_id with its the associated month of publication with:

SELECT title_id, 
CASE EXTRACT(month FROM pubdate) 
    WHEN 1 THEN 'Jan'
    WHEN 2 THEN 'Feb'
    WHEN  3 THEN 'Mar'
    WHEN  4 THEN 'Apr'
    WHEN  5 THEN 'May'
    WHEN  6 THEN 'Jun'
    WHEN  7 THEN 'Jul'
    WHEN  8 THEN 'Aug'
    WHEN  9 THEN 'Sep'
    WHEN  10 THEN 'Oct'
    WHEN  11 THEN 'Nov'
    ELSE 'Dec'
    END MONTH 
FROM TITLES;

Using the statement:

SELECT MAX(Most_Titles) 
FROM (SELECT count(title_id) Most_Titles, month 
       FROM (SELECT title_id, extract(month FROM pubdate) AS MONTH FROM titles) GROUP BY month);

I was able to determine the month with the maximum number of books published.

Is there a way to join the two statements so that I can associate the month's text equivalent with the maximum number of titles?

回答1:

In order to convert a month to a string, I wouldn't use a CASE statement, I'd just use a TO_CHAR. And you can use analytic functions to rank the results to get the month with the most books published.

SELECT num_titles,
       to_char( publication_month, 'Mon' ) month_str
  FROM (SELECT count(title_id) num_titles,
               trunc(pubdate, 'MM') publication_month,
               rank() over (order by count(title_id) desc) rnk
          FROM titles
         GROUP BY trunc(pubdate, 'MM'))
 WHERE rnk = 1

A couple of additional caveats

• If there are two months that are tied with the most publications, this query will return both rows. If you want Oracle to arbitrarily pick one, you can use the row_number analytic function rather than rank.
• If the PUBDATE column in your table only has dates of midnight on the first of the month where the book is published, you can eliminate the trunc on the PUBDATE column.