Re: Need help with SQL statement!

"Alex" <sh2222_at_yahoo.com> wrote in message news:_5lVa.117527$TJ.6968703_at_twister.austin.rr.com...
> I am having difficulty writing a SQL statement and I was wondering if one
 of
> you gurus could help me. I have a table that has three fields: InvoiceID,
> Item, ItemPrice. Each InvoiceID is associated with one or more Items. I am
> trying to write a SQL statement that returns the record (all three fields)
> for the least expensive item for each invoice (so the total number of
> records returned will be equal to the total number of unique Invoice IDs.
> How would I write this SQL statement?

SELECT tblInvoiceA.InvoiceID, tblInvoiceA.Item, tblInvoiceA.ItemPrice FROM tblInvoice AS tblInvoiceA
WHERE (((tblInvoiceA.ItemPrice)
IN
(SELECT MIN(ItemPrice)
FROM tblInvoice
WHERE InvoiceID = tblInvoiceA.InvoiceID)));

or an = would do just as well as the IN.

Problem - what if you have 2 items on the same invoice with the same price, which is also the lowest price on that invoice? Which do you want to choose? You could try this:

SELECT tblInvoiceA.InvoiceID, First(tblInvoiceA.Item) AS FirstOfItem, tblInvoiceA.ItemPrice
FROM tblInvoice AS tblInvoiceA
GROUP BY tblInvoiceA.InvoiceID, tblInvoiceA.ItemPrice HAVING (((tblInvoiceA.ItemPrice)
IN
(SELECT MIN(ItemPrice)
FROM tblInvoice
WHERE InvoiceID = tblInvoiceA.InvoiceID)));

Which will give you one 'lowest price' item.

But your table design is wrong. You want one table tblInvoices, in a one to many relationship with tblInvoiceItems. Or something. The way you've got it at the moment I can't see what your Primary Key is. InvoiceID + Item? What if they buy 2 of them on the same invoice?

Yours, Mike MacSween Received on Tue Jul 29 2003 - 06:18:28 CEST