and i must find for each code_w,the title and the number of books he sold but ONLY FOR THE MOST expensive book he wrote.
I did this
select distinct writer.code_w,
FIRST_VALUE (book.title)OVER(PARTITION BY writer.code_w)AS title,
COUNT(buy.code_book) OVER(PARTITION BY writer.code_w) AS bought
JOIN book ON book.code_book=writer.code_book JOIN buy ON book.code_book=buy.code_book
ORDER BY writer.code_w ASC
but instead of taking the number of the most expensive books they sold i take the number of all the books they sold.
eg w02 must give 4 books (code_book 07 as most expensive) but it gives me 10 books.