Re: Need help with SQL statement!

From: John Gilson <jag_at_acm.org>
Date: Tue, 29 Jul 2003 04:36:41 GMT
Message-ID: <tRmVa.42267$On.5778014_at_twister.nyc.rr.com>

"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

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 Received on Tue Jul 29 2003 - 06:36:41 CEST