SQL Query Help - Multiple Grouping Columns

[ligthausdigital]

Hello,

Here's what I'm trying to do: For each ISBN number in a catalog of used books, I need to find the lowest price book, and return the SKU and the price. Right now I can only get the price out.

My query looks like this:

SELECT isbn, min(price) as minprice FROM books GROUP BY isbn;

The data looks like this:

sku|price|isbn
1|7.00|1234
2|5.00|1234
3|9.00|1234
4|6.00|5678
5|4.00|5678
6|8.00|5678

So I get something like this:
isbn|minprice
1234|5.00
5678|4.00

Which is fine, but I need to also get the SKU associated with that particular price. I would like to have a query that gives me:

isbn|minprice|sku
1234|5.00|2
5678|4.00|5

Anyway, I don't know how to do it, and if anyone could shed some light on it for me, I'd appreciate it.
Thank you,
Morgan

 

[rac2]

Maybe this will do it.

First create a view showing the lowest price of every book.

CREATE VIEW lowest_price_per_book AS

  SELECT isbn, min(price) as minprice
    FROM books
    GROUP BY isbn

Then join the view to the table to get the sku.

SELECT a.isbn, b.minprice, a.sku
  FROM books a
  JOIN lowest_price_per_book b
    ON a.isbn = b.isbn
WHERE a.price = b.minprice

Please let me know whether this works.

 

[ligthausdigital]

Thank you! I can't use that exactly, but it got me to where I needed to be. It's in Access, so I tried doing what your statement said from the GUI. Here's the result if your curious. And thanks again, I truly appreciate it.

SELECT pricesearch.isbn, pricesearch.minprice, books.pkey
FROM pricesearch INNER JOIN books ON (pricesearch.minprice = books.price) AND (pricesearch.isbn = books.isbn);

Now that I look at it, it's exactly what you had, just INNER JOIN is different.