Select Records With Most Current Date

[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.

 

[Marshall Barton]

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

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

 

[Guest]

Thank you,

I will give it a try....

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