Need help with SQL statement!

[Alex]

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?



Thank you,



Alex

[D]

Re: Need help with SQL statement!

I would like to humbly state that, if I understand you correctly that you
want the cheapest item of every order, this is no simple matter in a Totals
Query. I had a similar need once where I wanted the cheapest (Min) of a
product price but I also wanted all the details of that price (Order #,
Date, Vendor, etc...). The problem you will have is with the Item field, by
introducing this third field you will wind up getting all the items. I do
not claim to be an expert but this is the problem that I had. I therefore
decided to use a PivotChart instead and found it to be very nice and neat.
I hope this helps and maybe there is someone out there that knows a better
way.

Dave

"Nic" <nicpayre[junk]@sympatico.ca> wrote in message
news:qolVa.1889 $Cx4.389105@new s20.bellglobal. com...[color=blue]
>
> "Alex" <sh2222@yahoo.c om> wrote in message
> news:_5lVa.1175 27$TJ.6968703@t wister.austin.r r.com...[color=green]
> > I am having difficulty writing a SQL statement and I was wondering if[/color][/color]
one[color=blue]
> of[color=green]
> > you gurus could help me. I have a table that has three fields:[/color][/color]
InvoiceID,[color=blue][color=green]
> > Item, ItemPrice. Each InvoiceID is associated with one or more Items. I[/color][/color]
am[color=blue][color=green]
> > trying to write a SQL statement that returns the record (all three[/color][/color]
fields)[color=blue][color=green]
> > 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[/color][/color]
IDs.[color=blue][color=green]
> > How would I write this SQL statement?
> >[/color]
> What you need is a group function...i let you guess wich one it is.[color=green]
> >
> >
> > Thank you,
> >
> >
> >
> > Alex
> >
> >[/color]
>
>[/color]

[Mike MacSween]

Re: Need help with SQL statement!

"Alex" <sh2222@yahoo.c om> wrote in message
news:_5lVa.1175 27$TJ.6968703@t wister.austin.r r.com...[color=blue]
> I am having difficulty writing a SQL statement and I was wondering if one[/color]
of[color=blue]
> 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?[/color]

SELECT tblInvoiceA.Inv oiceID, tblInvoiceA.Ite m, tblInvoiceA.Ite mPrice
FROM tblInvoice AS tblInvoiceA
WHERE (((tblInvoiceA. ItemPrice)
IN
(SELECT MIN(ItemPrice)
FROM tblInvoice
WHERE InvoiceID = tblInvoiceA.Inv oiceID)));

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.Inv oiceID, First(tblInvoic eA.Item) AS FirstOfItem,
tblInvoiceA.Ite mPrice
FROM tblInvoice AS tblInvoiceA
GROUP BY tblInvoiceA.Inv oiceID, tblInvoiceA.Ite mPrice
HAVING (((tblInvoiceA. ItemPrice)
IN
(SELECT MIN(ItemPrice)
FROM tblInvoice
WHERE InvoiceID = tblInvoiceA.Inv oiceID)));

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

[John Gilson]

Re: Need help with SQL statement!

"Alex" <sh2222@yahoo.c om> wrote in message news:_5lVa.1175 27$TJ.6968703@t wister.austin.r r.com...[color=blue]
> 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?
>
>
>
> Thank you,
>
>
>
> Alex[/color]

CREATE TABLE Invoices
(
invoice_id INT NOT NULL,
item INT NOT NULL,
item_price DECIMAL(6, 2) NOT NULL,
PRIMARY KEY (invoice_id, item)
)

-- Sample data
INSERT INTO Invoices (invoice_id, item, item_price)
VALUES (1, 1, 10)
INSERT INTO Invoices (invoice_id, item, item_price)
VALUES (1, 2, 15.50)
INSERT INTO Invoices (invoice_id, item, item_price)
VALUES (1, 3, 9.99)
INSERT INTO Invoices (invoice_id, item, item_price)
VALUES (2, 1, 99.99)
INSERT INTO Invoices (invoice_id, item, item_price)
VALUES (2, 2, 149.99)

SELECT I1.invoice_id, I1.item, I1.item_price
FROM Invoices AS I1
LEFT OUTER JOIN
Invoices AS I2
ON I1.invoice_id = I2.invoice_id AND
I2.item_price < I1.item_price
WHERE I2.item_price IS NULL
ORDER BY I1.invoice_id

invoice_id item item_price
1 3 9.99
2 1 99.99

Regards,
jag

[Leader]

Re: Need help with SQL statement!

"Alex" <sh2222@yahoo.c om> wrote in message news:<_5lVa.117 527$TJ.6968703@ twister.austin. rr.com>...[color=blue]
> 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?
>
>
>
> Thank you,
>
>
>
> Alex[/color]


Hi,
I think you should try with Group by clauseand MIN function. it might help you.

Thanks
Hoque

[Simon Hayes]

Re: Need help with SQL statement!

"Alex" <sh2222@yahoo.c om> wrote in message news:<_5lVa.117 527$TJ.6968703@ twister.austin. rr.com>...[color=blue]
> 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?
>
>
>
> Thank you,
>
>
>
> Alex[/color]

It's not clear what the best way to do this is, without knowing what
your table structure is, what your keys are etc. For example, what
happens if one invoice has two items with the same 'minimum' price -
which item should appear on the output?

If one invoice has many items, you would usually normalize your design
to have an Invoices table and an InvoiceItems table, with a foreign
key linking them. You'd probably also need to link to some sort of
Items table which has all the items that could appear on an invoice.
This is a lot more efficient - invoice tables are a common task, so
you can probably find some information on the web about implementing
them.

Having said all that, here's one query that _might_ work, but it won't
be consistent and may be inefficient, depending on what indexes you
have on the table. The real solution is almost certainly to redesign
your tables, but without more background information it's not clear.

select
it.InvoiceID,
it.Item,
it.ItemPrice
from
InvoiceTable it
join ( select InvoiceID, min(ItemPrice) as ItemPrice
from InvoiceTable
group by InvoiceID ) dt
on it.InvoiceID = dt.InvoiceID
and it.ItemPrice = dt.ItemPrice
order by
it.InvoiceID

Simon

[Alex]

Re: Need help with SQL statement!

Thank you all for your help, particularly Mike MacSween's.

"Alex" <sh2222@yahoo.c om> wrote in message
news:_5lVa.1175 27$TJ.6968703@t wister.austin.r r.com...[color=blue]
> I am having difficulty writing a SQL statement and I was wondering if one[/color]
of[color=blue]
> 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?
>
>
>
> Thank you,
>
>
>
> Alex
>
>[/color]

[Alex]

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@bti nternet.com> wrote in message
news:3f25f534$0 $56606$bed64819 @pubnews.gradwe ll.net...[color=blue]
> "Alex" <sh2222@yahoo.c om> wrote in message
> news:_5lVa.1175 27$TJ.6968703@t wister.austin.r r.com...[color=green]
> > I am having difficulty writing a SQL statement and I was wondering if[/color][/color]
one[color=blue]
> of[color=green]
> > you gurus could help me. I have a table that has three fields:[/color][/color]
InvoiceID,[color=blue][color=green]
> > Item, ItemPrice. Each InvoiceID is associated with one or more Items. I[/color][/color]
am[color=blue][color=green]
> > trying to write a SQL statement that returns the record (all three[/color][/color]
fields)[color=blue][color=green]
> > 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[/color][/color]
IDs.[color=blue][color=green]
> > How would I write this SQL statement?[/color]
>
> SELECT tblInvoiceA.Inv oiceID, tblInvoiceA.Ite m, tblInvoiceA.Ite mPrice
> FROM tblInvoice AS tblInvoiceA
> WHERE (((tblInvoiceA. ItemPrice)
> IN
> (SELECT MIN(ItemPrice)
> FROM tblInvoice
> WHERE InvoiceID = tblInvoiceA.Inv oiceID)));
>
> or an = would do just as well as the IN.
>
> Problem - what if you have 2 items on the same invoice with the same[/color]
price,[color=blue]
> which is also the lowest price on that invoice? Which do you want to[/color]
choose?[color=blue]
> You could try this:
>
> SELECT tblInvoiceA.Inv oiceID, First(tblInvoic eA.Item) AS FirstOfItem,
> tblInvoiceA.Ite mPrice
> FROM tblInvoice AS tblInvoiceA
> GROUP BY tblInvoiceA.Inv oiceID, tblInvoiceA.Ite mPrice
> HAVING (((tblInvoiceA. ItemPrice)
> IN
> (SELECT MIN(ItemPrice)
> FROM tblInvoice
> WHERE InvoiceID = tblInvoiceA.Inv oiceID)));
>
> Which will give you one 'lowest price' item.
>
> But your table design is wrong. You want one table tblInvoices, in a one[/color]
to[color=blue]
> many relationship with tblInvoiceItems . Or something. The way you've got[/color]
it[color=blue]
> 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
>
>
>[/color]