Results 1 to 5 of 5

Thread: most expensive

  1. #1
    Join Date
    Feb 2009
    Posts
    38

    Unanswered: most expensive

    Hi i have these tables:

    BUY

    code_client,code_book
    pl02,b 08
    pl11,b 02
    pl05,b 08
    pl08,b 01
    pl10,b 03
    pl02,b 01
    pl02,b 03
    pl10,b 02
    pl10,b 05
    pl04,b 06
    pl02,b 02
    pl12,b 01
    pl06,b 08
    pl07,b 01
    pl10,b 07
    pl02,b 07
    pl03,b 01
    pl01,b 07
    pl02,b 06
    pl10,b 04
    pl07,b 07
    pl10,b 01
    pl04,b 02

    WRITER

    code_writer,code_book
    w01, b04
    w02, b02
    w02, b03
    w02 ,b07
    w03, b01
    w04, b03
    w05 ,b04
    w06 ,b07
    w07 ,b04
    w08 ,b06
    w09 ,b07
    w10 ,b05
    w11 ,b01
    w11 ,b08

    and

    BOOK

    CODE_BOOK, title,price
    b01,a ,120
    b02,b , 60
    b03,c ,70
    b04,d ,100
    b05,e , 80
    b06,f, 80
    b07,g , 90
    b08,h , 90

    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
    FROM writer
    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.

    Helpppppppppppppppppppppp

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    instead of taking the number of the most expensive books they sold i take the number of all the books they sold.
    So why don't you include that condition into the WHERE clause?

  3. #3
    Join Date
    Feb 2009
    Posts
    38
    Quote Originally Posted by Littlefoot
    So why don't you include that condition into the WHERE clause?
    i do that but maybe i do it wrong because it appears only 2 writers with the most expensive book, instead of all of them

  4. #4
    Join Date
    Feb 2009
    Posts
    38
    Ok did it

    Max(count ....) instead of count

    and group by

  5. #5
    Join Date
    Jan 2009
    Location
    Dhaka, Bangladesh
    Posts
    51
    please clear up what kind of output you want
    Mohammad Hasan Shaharear
    E-mail
    Blog: http://shaharear.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •