create a query showing records that are less than the max date

Mark Drelinger
Mark Drelinger used Ask the Experts™
on
I have a table with three columns (ID, ProductIDNo, DateRecieved).
ProductIDNo field has duplicates with different DateREceived values.
I want to filter it to include all records that are less than the Max DateReceived for that record.
Is this possible?
C--Users-Drelinger.D179434-Desktop-C.JPG
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Top Expert 2016
Commented:
try this query


SELECT T.*
FROM yourTable T
Left Join
(SELECT yourTable.ProductIDNo, Max(yourTable.DateReceived) AS MaxOfDateReceived
FROM yourTable
GROUP BY yourTable.ProductIDNo) As T1
ON T.DateReceived=T1.MaxOfDateReceived
Where T1.MaxOfDateReceived Is Null
Mark DrelingerMIS

Author

Commented:
wow. that worked amazing. thank you.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial