none
Select field with highest date RRS feed

  • 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

    Thursday, December 19, 2019 10:21 AM

Answers

  • 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)

    • Marked as answer by Lteu Friday, December 20, 2019 12:27 PM
    Thursday, December 19, 2019 12:16 PM

All replies

  • 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)

    • Marked as answer by Lteu Friday, December 20, 2019 12:27 PM
    Thursday, December 19, 2019 12:16 PM
  • thank you Hans, it works
    Friday, December 20, 2019 12:26 PM