Results 1 to 9 of 9

Thread: maximum price

  1. #1
    Join Date
    Feb 2009
    Posts
    38

    Unanswered: maximum price

    hi i have a tiny problem:
    i have three tables and want for each code of a writer to find the title of the most expensive book and the count of books that have been sold per writer (without the price to be apparent) but for some reason cant do it. i did for starters s/thing like this but it's not what i m' looking for. Dont know where to put the most expensive to dinstinct the writers



    SELECT WRITER.CODE_W,BOOK.TITLE,COUNT (BUY.CODE_B)
    FROM WRITER
    LEFT OUTER JOIN BOOK
    ON BOOK.CODE_B=WRITER.CODE_B
    INNER JOIN BUY ON WRITER.CODE_B=BUY.CODE_B
    WHERE (EXISTS (SELECT distinct MAX(book.price) FROM book))
    GROUP BY WRITER.CODE_W,BOOK.TITLE


    It still gives me all the prices
    eg
    code_w,title,count
    w02,history,3
    w02,maths,4
    w01,physik,5

    and i only want each writer with his most expensive book
    not with all books eg

    w02,history,3
    w01,physik,5


    my tables are :
    writer, Book,buy

    in writer i have
    code_w and code_b

    in book i have
    code_b,title,price

    and in buy i have
    code_client,code_b


    Thanks in advance
    Last edited by misty1976; 02-13-09 at 03:19.

  2. #2
    Join Date
    Feb 2009
    Posts
    38
    noone knows??

  3. #3
    Join Date
    Dec 2008
    Posts
    99
    Quote Originally Posted by misty1976
    hi i have a tiny problem:
    i have three tables and want for each code of a writer to find the title of the most expensive book and the count of books that have been sold per writer (without the price to be apparent) but for some reason cant do it. i did for starters s/thing like this but it's not what i m' looking for. Dont know where to put the most expensive to dinstinct the writers



    SELECT WRITER.CODE_W,BOOK.TITLE,COUNT (BUY.CODE_B)
    FROM WRITER
    LEFT OUTER JOIN BOOK
    ON BOOK.CODE_B=WRITER.CODE_B
    INNER JOIN BUY ON WRITER.CODE_B=BUY.CODE_B
    WHERE (EXISTS (SELECT distinct MAX(book.price) FROM book))
    GROUP BY WRITER.CODE_W,BOOK.TITLE


    It still gives me all the prices
    eg
    code_w,title,count
    w02,history,3
    w02,maths,4
    w01,physik,5

    and i only want each writer with his most expensive book
    not with all books eg

    w02,history,3
    w01,physik,5


    my tables are :
    writer, Book,buy

    in writer i have
    code_w and code_b

    in book i have
    code_b,title,price

    and in buy i have
    code_client,code_b


    Thanks in advance

    Hello,

    select writer,book
    from abc
    where book_id (select max(price) from xyz)

    it must be something like this

    do you really need to use sql joins?

    Kind Regards

  4. #4
    Join Date
    Jan 2009
    Posts
    17
    SELECT WRITER.CODE_W,BOOK.TITLE,COUNT (BUY.CODE_B)
    FROM WRITER
    LEFT OUTER JOIN BOOK
    ON BOOK.CODE_B=WRITER.CODE_B
    INNER JOIN BUY ON WRITER.CODE_B=BUY.CODE_B
    WHERE (EXISTS (SELECT distinct MAX(book.price) FROM book group by CODE_B))
    GROUP BY WRITER.CODE_W,BOOK.TITLE

  5. #5
    Join Date
    Feb 2009
    Posts
    38
    Quote Originally Posted by sunsail
    Hello,

    select writer,book
    from abc
    where book_id (select max(price) from xyz)

    it must be something like this

    do you really need to use sql joins?

    Kind Regards
    It says: MISSING EXPRESSION

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Try this one:
    Code:
    SELECT distinct w.code_w,
           first_value(bk.title) over (partition by w.code_w order by bk.price desc) as most_expensive_book,
           count(b.code_b) over (partition by w.code_w) as total_books_sold
    FROM writer w
     JOIN book bk ON bk.code_b = w.code_b
     JOIN buy b ON b.code_b = w.code_b;

  7. #7
    Join Date
    Feb 2009
    Posts
    38
    Quote Originally Posted by shammat
    Try this one:
    Code:
    SELECT distinct w.code_w,
           first_value(bk.title) over (partition by w.code_w order by bk.price desc) as most_expensive_book,
           count(b.code_b) over (partition by w.code_w) as total_books_sold
    FROM writer w
     JOIN book bk ON bk.code_b = w.code_b
     JOIN buy b ON b.code_b = w.code_b;
    I must have book title also. If i do this:
    SELECT distinct w.code_w,book.title
    first_value(bk.title) over (partition by w.code_w order by bk.price desc) as most_expensive_book,
    count(b.code_b) over (partition by w.code_w) as total_books_sold
    FROM writer w
    JOIN book bk ON bk.code_b = w.code_b
    JOIN buy b ON b.code_b = w.code_b;

    i still take all the writers with all their books

  8. #8
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by misty1976
    I must have book title also.
    I don't understand, my SELECT shows the book title...

  9. #9
    Join Date
    Feb 2009
    Posts
    38
    Quote Originally Posted by shammat
    I don't understand, my SELECT shows the book title...
    Yes but i didnt put it. i put book price instead. I must wear glasses. My mistake. Yours works just fine. Many many thanks.

Posting Permissions

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