0

I have created a table Books with the following attributes: Books(BookID, BookName, Author, Price)

Now I wish to find the name of the book which has the least price(cheapest book's name), along with its price.

To do so, I did something like:

SELECT min(Price) FROM Books;

This gave me the minimum price ($55.36). And then I used:

SELECT BookName,Price FROM Books 
WHERE Price=55.36;

This gave me the answer I was looking for...

But I want to ask, is there a better way, so that I don't need to know the minimum price before finding the answer?Can I do it in a single query?

I tried something like:

SELECT BookName,Price FROM Books
WHERE Price=min(Price);

But I got an Invalid use of Group function error. Any help would be appreciated. I am using MySQL Server version 8.0.

Thanks!

A Suresh
  • 21
  • 3

1 Answers1

1

In a Derived Table, you can determine the minimum price value out of all the books. Now, you can join this result-set back to the main table Books on minimum Price value, to get the row having lowest price.

SELECT b1.BookName, 
       b1.Price 
FROM Books AS b1 
JOIN (SELECT MIN(b2.Price) AS min_price 
      FROM Books AS b2
     ) AS dt ON dt.min_price = b1.Price

Another alternative approach is using a Subquery. In a subquery, determine the minimum price value, and use WHERE .. IN(..) to determine the rows having lowest price value:

SELECT b1.BookName, 
       b1.Price 
FROM Books AS b1 
WHERE b1.Price IN (SELECT MIN(b2.Price)
                   FROM Books AS b2)
Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57