Trouble with SQL query using MAX function

I am having trouble trying to get a query to work that involves multiple tables and the use of the MAX function. I’m pretty sure a subquery is needed to get it to work but am having trouble figuring out how to do this.

I basically have three tables—Item, InvItem, and Invoice. The “Item” table keeps track of items that are for sale, the “Invoice” is an actual sales transaction, and “InvItem” are the individual items that are sold at each Invoice.

What I am trying to do is to return the last date that an item was last sold on and the number of the items sold during that last invoice for the particular item. I have no trouble getting the last date an item was sold on, but can’t figure out how to get the quantity of the item sold on that date. Here is the SQL to get the last date an item was sold on:

SELECT Max(Invoice.InvoiceDate) AS MaxOfInvoiceDate, InvItem.ItemID
FROM InvItem INNER JOIN Invoice ON InvItem.InvoiceID = Invoice.InvoiceID
GROUP BY InvItem.ItemID;

This gives me every "ItemID" that was sold on an invoice and the date of that invoice.  However I can’t add “InvItem.Qty” to the SELECT statement since it isn’t going to be unique. I would think that I would need to do a subquery here but I’m a little unsure how to do it. I’ve done subqueries in the WHERE clause before on a single field but I would think I would need to use two fields here but I'm not even sure that would work either.

Does anyone have any suggestions? Any help is much appreciated. Thanks!
rsts_supportAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
a sample db with the relevant table will help..
Jim HornMicrosoft SQL Server Data DudeCommented:
>This gives me every "ItemID" that was sold on an invoice and the date of that invoice.
Actually MAX states that it only gives the most recent invoice date, not all invoice dates.

>However I can’t add “InvItem.Qty” to the SELECT statement since it isn’t going to be unique.
So tell us what you're looking for, most recent invoice date and count/sum of Qty? Specifically, eyeball SQL Server GROUP BY Solutions, part 5. Aggregate AND values from a single row that make up the aggregate: Subquery, and let us know if that's what you're after.
rsts_supportAuthor Commented:
Jim Horn, MAX gives me the most recent invoice date PER item (e.g. screws, bolts, etc)  which is what I want (the "Group By ItemID" does this). In other words if item X was sold on two separate invoices (hence two separate dates) which is the more recent of the two dates?

What I am looking for is the not only the last date that a particular item was sold but also the quantity that was sold of that item on that date. I don't need to use an aggregate function on "Qty" because I just need a one record value for Quantity. So if Item A was last sold on  6/7/2015 on Invoice B, and there were 5 units of it sold, then the "Qty" would be 5. The user is seeking this information so they can answer the question "When was the last time that item X was sold and how many of it were sold?. If it was a LONG time ago that item X was last sold, and there weren't very many units of it sold during that transaction, then maybe I shouldn't be CARRYING that item any longer..."

I created an Access database example that parrots the actual vendor database just so I can figure out how to solve the issue. I've attached the Access database to this message. In this database take a look at the query "LastDatebyItem". This selects the last date per item of each item type sold. Along with this I want to get the actual "Qty" sold from the "InvItem" table as well. This is what I can't figure out how to do.
TestDB.accdb
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Rey Obrero (Capricorn1)Commented:
try this query


SELECT B.CodeID, B.Qty, B.InvoiceDate
FROM
(SELECT InvItem.InvoiceID, InvItem.Qty, Invoice.InvoiceDate, InvItem.CodeID
FROM InvItem INNER JOIN Invoice ON InvItem.InvoiceID = Invoice.InvoiceID) As B
 INNER JOIN
 (SELECT Max(Invoice.InvoiceDate) AS MaxOfInvoiceDate, InvItem.CodeID
FROM InvItem INNER JOIN Invoice ON InvItem.InvoiceID = Invoice.InvoiceID
GROUP BY InvItem.CodeID) As C
 ON (B.CodeID = C.CodeID) AND (B.InvoiceDate = C.MaxOfInvoiceDate)
ORDER BY B.CodeID

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rey Obrero (Capricorn1)Commented:
here is your sample db, see query2
TestDB.accdb
rsts_supportAuthor Commented:
You nailed it Rey! Thank you so much!

I kept thinking in terms of using the WHERE clause but I didn't think about doing a JOIN on both of those fields.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.