CODEWITHSUNDEEP
sqloracle-database
Yoyo Me
Yoyo Me

Reputation: 9

Query to get most expensive book per author

How to write a sql query in Oracle to get results for:

Table "books" has five columns: BOOK_ID, BOOK_TITLE, BOOK_AUTHOR, PRICE, PUBLISH_DATE. Print list of most expensive book per author published during last 300 days ? 

select book_author, 
       publish_date 
  from books 
 where max(price) and publish_date between (sysdate-300) and sysdate;

Upvotes: 0

Views: 5148

Answers (2)

mechanical_meat
mechanical_meat

Reputation: 169304

There are several ways to do accomplish your goal. This is a self-exclusion join and should be fairly efficient. It basically says return me the row(s) with a price for which there is no higher price; for each author. There is a tested example here: http://sqlfiddle.com/#!4/86b22/1 

select 
    b.book_author, b.publish_date, b.price
from 
    books b
    left join books b2
    on b.book_author = b2.book_author
    and b2.price > b.price
    and b2.publish_date between (sysdate-300) and sysdate
where 
    b.publish_date between (sysdate-300) and sysdate
    and b2.book_id is null;

In case you are interested, more examples of this type of query can be found on my blog: http://adam-bernier.appspot.com/post/38001/the-self-exclusion-join

Upvotes: 1

Doug Porter
Doug Porter

Reputation: 7897

You could use analytics to accomplish this as well:

select * 
  from (select book_id,
               book_author,
               price,
               publish_date,
               row_number() over (partition by book_author order by price desc) rn
          from books
         where publish_date >= (sysdate - 300)
       )
 where rn = 1;

Upvotes: 1

Related Questions