Group By and Max 2

[UncleHuckleberry]

I have a table called tblCodes:

ItemNo, CodeNo, DateEntered
1 1 12/10/2004
1 2 13/10/2004
1 3 14/10/2004

I want a query that shows the latest DateEntered for each ItemNo and the corresponding CodeNo to go with it. If I have a MAX on date entered and a GROUP BY on CodeNo it shows all the CodeNo's for each ItemNo at the moment.

Thanks.

"When the going gets wierd, the wierd turn pro" - R. Duke

 

[r937]

select ItemNo
     , CodeNo
     , DateEntered
  from tblCodes as X
 where DateEntered  
     = ( select max(DateEntered)
           from tblCodes
          where CodeNo = X.CodeNo )

rudy

http://r937.com/

SQL Consulting

 

[lespaul]

another option (if you are using a newer version of access (2000+)) is like this:

SELECT ItemNo, CodeNo, DateEntered
  FROM tblCodes As X
  INNER JOIN (SELECT ItemNo, Max(DateEntered) As DateEntered FROM tblCodes GROUP BY ItemNo) As Y ON X.ItemNo = Y.ItemNo AND X.DateEntered = Y.DateEntered

Leslie

 

[r937]

i'm curious as to which of those two versions performs better...

rudy

http://r937.com/

SQL Consulting