SQL Query Help

[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

 

[MattSmithProg]

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

No two nulls are the same

 

[ligthausdigital]

Thanks, but maybe I didn't make it clear. Even books with the same ISBN have different SKUs. When I add sku to the GROUP BY clause, it ends up showing every book, just sorted differently. I need to show only one book per ISBN.
Thanks,
Morgan

 

[lespaul]

try select unique isbn, etc...

 

[tlbroadbent]

Try something like this.

Select * From books
Inner Join
(SELECT isbn, min(price) as minprice
FROM books
GROUP BY isbn) As qry

On books.isbn=qry.isbn
And books.Price=qry.minprice;

Or this...

Select * From books
Where Price=
(SELECT min(price)
FROM books
Where isbn=books.isbn) Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.