Re: Need help with SQL statement!

Mike,

Thank you so much for your reply. Your solution is exactly what I was looking for. You definitely know your SQL.

Alex

"Mike MacSween" <mike.macsween.damnthespam_at_btinternet.com> wrote in message news:3f25f534$0$56606$bed64819_at_pubnews.gradwell.net...
> "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 - 12:25:56 CEST