w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
Write a query to display the book code, book title ,supplier name and price of the book which takes maximum price based on each supplier

The first step is to get the maximum price per supplier:

SELECT  supplier_id, MAX(Price) AS Price
FROM    lms_book_details
GROUP BY supplier_id

You then have your query to get all book details

SELECT  bd.book_code,
        bd.book_title,
        sd.supplier_name,
        bd.price
FROM    lms_book_details AS bd
        JOIN lms_suppliers_details AS sd 
            ON sd.supplier_id = bd.supplier_id;

So it is just a matter of combining the two to filter the results of the second query to only include the results of the first:

SELECT  bd.book_code,
        bd.book_title,
        sd.supplier_name,
        bd.price
FROM    lms_book_details AS bd
        JOIN lms_suppliers_details AS sd 
            ON sd.supplier_id = bd.supplier_id
        JOIN
        (   SELECT  supplier_id, MAX(Price) AS Price
            FROM    lms_book_details
            GROUP BY supplier_id
        ) AS MaxPrice
            ON MaxPrice.supplier_id = bd.supplier_id
            AND MaxPrice.Price = bd.Price;

ADDENDUM

After recreating your sample data on SQL Fiddle, then running the query above you get the following results

BOOK_CODE   BOOK_TITLE                  SUPPLIER_NAME      
PRICE
BL0000002   Java The compete reference  ROSE BOOK STORE     750
BL0000006   Java The compete reference  ROSE BOOK STORE     750
BL0000004   Java The compete reference  SINGAPORE SHOPPEE   750
BL0000009   Fire                        KAVARI STORE        999

I assume "ROSE BOOK STORE" is the duplicate you are referring to, however taking your instruction:

Write a query to display the book code, book title ,supplier name and price of the book witch takes maximum price based on each supplier.

The maximum price for "ROSE BOOK STORE" is 750.00, so we need to get the book_code, book_title for the book which takes this price. There are however two books that take this price, which one should be chosen? With no guidelines on how to make this choice, both books with this price are returned.





© Copyright 2018 w3hello.com Publishing Limited. All rights reserved.