how to use SQL group to filter rows with maximum date value

Select the maximum dates for each id.

select id, max(adate) max_datefrom testgroup by id

Join on that to get the rest of the columns.

select t1.*from test t1inner join (select id, max(adate) max_date            from test            group by id) t2on t1.id = t2.id and t1.adate = t2.max_date;

If you are using a DBMS that has analytical functions you can use ROW_NUMBER:

SELECT  Id, Value, ADateFROM    (   SELECT  ID,                    Value,                    ADate,                    ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Adate DESC) AS RowNum            FROM    Test        ) AS TWHERE   RowNum = 1;

Otherwise you will need to use a join to the aggregated max date by Id to filter the results from Test to only those where the date matches the maximum date for that Id

SELECT  Test.Id, Test.Value, Test.ADateFROM    Test        INNER JOIN        (   SELECT  ID, MAX(ADate) AS ADate            FROM    Test            GROUP BY ID        ) AS MaxT            ON MaxT.ID = Test.ID            AND MaxT.ADate = Test.ADate;

Please try:

select   * from   tbl awhere   a.adate=(select MAX(adate) from tbl b where b.Id=a.Id)