Hi

I have two tables, each with the following structure:


INVOICE Table

INVOICE_ID       int
INVOICE_DATE     datetime (DD/MM/YY)
CUSTOMER         string

INVOICE_ITEM Table

INVOICE_ID       int
ITEM_NUMBER      int
DESCRIPTION      string
UNIT_PRICE       float
QUANTITY         int


And the following example data in them:


INVOICE Table

1  30/01/09   CUSTOMER1
2  30/02/09   CUSTOMER1
3  30/03/09   CUSTOMER1

INVOICE_ITEM Table

1  1  SCREWDRIVER  10.00  5
1  2  HAMMER       11.00  1
1  3  DRILL        23.00  10
2  1  SCREWDRIVER  11.00  4
2  2  HAMMER       12.00  2
2  3  DRILL        24.00  11
3  1  SCREWDRIVER  10.50  6
3  2  HAMMER       11.50  3
3  3  DRILL        23.50  10


Between these two tables, the history of all the invoices to all customers is held, together with what items were on the invoices and at what price they were sold each time.

I'd like to construct a query which will give me the price at which an item was last sold, together with all the other details that go with that sale such as the invoice ID, quantity, customer name, etc. For example, if I wanted the latest price for a HAMMER sold to CUSTOMER1, I'd like the results to look like this:


3  30/03/09  CUSTOMER1  2  HAMMER  11.50  3


Where the columns are as follows:


INVOICE.INVOICE_ID
INVOICE.INVOICE_DATE
INVOICE.CUSTOMER
INVOICE_ITEM.ITEM_NUMBER
INVOICE_ITEM.DESCRIPTION
INVOICE_ITEM.UNIT_PRICE
INVOICE_ITEM.QUANTITY


I've got as far as returning just the item description and the date of the latest invoice with the following query:


SELECT
  INVOICE_ITEM.DESCRIPTION,
  MAX(INVOICE.INVOICE_DATE)
FROM
  INVOICE,
  INVOICE_ITEM
WHERE
  INVOICE.INVOICE_ID = INVOICE_ITEM.INVOICE_ID
GROUP BY
  INVOICE_ITEM.DESCRIPTION


But I'm struggling to get all the other columns into the results set. I've tried various attempts using GROUP BY's and sub queries without much success. Can anyone suggest what I need to do? I'm pretty much a SQL beginner. Unfortunately I can't change the database structure at all, so that's out of the question.

Many thanks

Daniel