"Alex" <sh****@yahoo.com> wrote in message news:<_5*********************@twister.austin.rr.co m>...
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
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
