Select Records With Most Current Date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table that I need to pull only the max date for each item. I will
give an example below.

Item Unit of Measure UPC Effective Date
3 EA 111 8/8/2006
3 EA 222 2/5/2006
3 EA 333 3/21/2005

5 EA 888 10/21/2006
5 EA 999 8/26/2006

With this example, the result set I would be looking for would be:

Item Unit of Measure UPC Effective Date
3 EA 111 8/8/2006
5 EA 888 10/21/2006

I have tried several methods with the DMAX function and used SQL, but I
can't seem to get a handle on it.

Any help would be greatly appreciated.
 
fvlmasl2 said:
I have a table that I need to pull only the max date for each item. I will
give an example below.

Item Unit of Measure UPC Effective Date
3 EA 111 8/8/2006
3 EA 222 2/5/2006
3 EA 333 3/21/2005

5 EA 888 10/21/2006
5 EA 999 8/26/2006

With this example, the result set I would be looking for would be:

Item Unit of Measure UPC Effective Date
3 EA 111 8/8/2006
5 EA 888 10/21/2006
Click to expand...


SELECT item, unit, upc, effectivedate
FROM table
WHERE effectivedate = (SELECT Max(effectivedate)
FROM table As X
WHERE X.item = table.item)
 
Thank you,

I will give it a try....

Marshall Barton said:
SELECT item, unit, upc, effectivedate
FROM table
WHERE effectivedate = (SELECT Max(effectivedate)
FROM table As X
WHERE X.item = table.item)
Click to expand...
 
Back
Top