Max() ? 1

[matrixindicator]

To simplify the question.

2009-01-01 | JEF | 1
2005-01-01 | ANN | 1
2001-01-01 | PET | 2

What I need, try to do is a query result where he group the number and take the highest date of each number.

The result I wish :

2009-01-01 | JEF | 1
2001-01-01 | PET | 2

But I got :

2009-01-01 | ANN | 1
2001-01-01 | PET | 2

ANN is not corresponding with the date, 2009-01-01, it should be JEF
I use the Max() function on date

 

[ingresman]

Could you enlighten us with the query and a table defintion ?

 

[matrixindicator]

$sql = "SELECT Max(tvme02_eig.EAkte) AS EEAkte, tvme02_eig.EEig, tvme01_app.App  FROM tvme02_eig INNER JOIN tvme01_app ON tvme02_eig.ErefApp = tvme01_app.ID_APP GROUP BY tvme02_eig.ErefApp";

EAkte : is a date

 

[r937]

SELECT tvme02_eig.EAkte
     , tvme02_eig.EEig
     , tvme01_app.App  
  FROM tvme02_eig 
INNER 
  JOIN ( SELECT MAX(EAkte) AS EEAkte
              , ErefApp
           FROM tvme02_eig
         GROUP
             BY ErefApp ) AS m
    ON m.ErefApp = tvme02_eig.ErefApp
   AND m.EEAkte = tvme02_eig.EAkte
INNER
  JOIN tvme01_app 
    ON tvme01_app.ID_APP = tvme02_eig.ErefApp

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[matrixindicator]

My idea said it has something to do with a sub-query. I did a first test and now it seem to work correctly txs !