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?
>
>
>
> 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 Received on Tue Jul 29 2003 - 10:25:53 CEST