Highest Values in Query

Hi All

I'm trying to create a query to get a list of purchase items that shows only the last time an item was purchased. The Table is a Purchase Item table where there are many duplicates.

I have a date field for each purchase (so the records are not duplicates really) I only want the ones with the latest date where there are duplicate discription, price and referance number.

How?
Derek BrownMDAsked:
Who is Participating?
 
mbizupConnect With a Mentor Commented:
Use a grouping query:

SELECT ItemPurchased, Max(PurchaseDate) AS MostRecent
FROM YourTable
GROUP BY  ItemPurchased
0
 
Rey Obrero (Capricorn1)Commented:
select *
from [Purchase Item]
where [date] =(select max([date]) from [Purchase Item] as p where p.[referance number]=[Purchase Item].[referance number]

you can use an itemID or productid if there is one, in place of [reference number]
0
 
mbizupCommented:
Or if you want to include all those fields:


SELECT ItemPurchased, description, Price, RefNo, Max(PurchaseDate) AS MostRecent
FROM YourTable
GROUP BY  ItemPurchased, description, Price, RefNo
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Derek BrownMDAuthor Commented:
Just as a matter of interest is it possible to get the latest 2 records from the duplicates?.

Brilliantly simple. Thank you
0
 
mbizupCommented:
So the top two records for each 'group'?

That is definitely doable, but a little more complicated.

This page has a good explanation:
http://www.sql-ex.ru/help/select16.php

The first solution in that thread will work in Access SQL.

The second does not - I believe it is specific to SQL Server.
0
 
Derek BrownMDAuthor Commented:
Thank you.That's interesting.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.