Select field with highest date

Question

Hi,

I have a table A with an ID and a comment field. This table is in relation with table B with an id and a date field and a comment field.



I want to make a query with A.field1 and B.field1 where B.Field1 has the highest F_date

SELECT A.Field1, Max(B.F_Date) AS F_Date, First(B.Field1) AS Field1
FROM A INNER JOIN B ON A.ID = B.id
GROUP BY A.Field1
ORDER BY A.Field1, Max(B.F_Date) DESC; 

is not OK

Answer 1

SELECT A.Field1, B.F_Date, B.Field1
FROM A INNER JOIN B ON A.ID = B.id
WHERE B.F_Date = (SELECT Max(T.F_Date) FROM B AS T WHERE T.id = B.id)

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 2

thank you Hans, it works