×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Finding latest price records only

Finding latest price records only

Finding latest price records only

(OP)
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




 

RE: Finding latest price records only

SELECT  A.INVOICE_ID,A.INVOICE_DATE,A.CUSTOMER
,B.ITEM_NUMBER,B.DESCRIPTION,B.UNIT_PRICE,B.QUANTITY
FROM INVOICE A,INVOICE_ITEM B
,(SELECT D.DESCRIPTION,MAX(C.INVOICE_DATE) LastDate
  FROM INVOICE C,INVOICE_ITEM D
  WHERE C.INVOICE_ID=D.INVOICE_ID
  GROUP BY D.DESCRIPTION) E
WHERE B.DESCRIPTION=E.DESCRIPTION AND A.INVOICE_DATE=E.LastDate

Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close