-2

Tables: Book(isbn, title, author, stockQty, price)

Question:Find the title and the price cheapest book for each quantity we have (i.e. for each quantity, what is the cheapest price of a book in that quantity level)?

Writing the query in SQL.

I did:

SELECT B.title B.stockQty, MIN(B.price)
FROM book B
GROUP BY B.stockQty

But it won't show the correct title. (will only show the title of the first book)

Chang Ding
  • 3
  • 1
  • This is a classic "select top 1 per group" problem. See for example http://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group, and many, many others. – Martha Jul 01 '15 at 04:05
  • Why tag both MySQL and Access? Are you really using both products? (Please don't tag products not involved.) – jarlh Jul 01 '15 at 07:31

2 Answers2

3

Try this:

select b.title
      ,b.price
      ,b.stockQty
from book b
where not exists (select 1
                  from book b2
                  where b2.stockQty = b.stockQty
                  and b2.price<b.price)
ksa
  • 403
  • 1
  • 5
  • 15
-2

In my opinion, it seems there are collection of similar books with different prices, so you need the cheapest price. I will use the SELECT DISTINCT

    "SELECT DISTINCT B.title, B.stockQty
    FROM book B
    ORDER BY B.price"
Gideon Appoh
  • 678
  • 1
  • 6
  • 15
  • But this will return every book/price combination, just ordered from low to high price. – jarlh Jul 01 '15 at 07:37
  • @jarlh can you please read more about SELECT DISTINCT, because it seems you don't know what you are saying. – Gideon Appoh Jul 01 '15 at 08:15
  • I think I have a decent knowledge about SELECT DISTINCT... (DISTINCT means that duplicated rows are removed from the result.) So in this case also the most expensive titles will be returned - which is not really what OP asks for. – jarlh Jul 01 '15 at 09:19