max date records

Though, I personally prefer CTE for these cases but because it is already answered I will provide you another way to accomplish it

CREATE TABLE MyTable
(
  ID INT
  , ADDRESS VARCHAR(200)
  , DATEField DATE
 )
 INSERT INTO MyTABLE
 VALUES (1,'xyz','01/01/2013')
 , (1,'abd',' 01/01/2014')
,(2,'dfg','01/03/2015')



SELECT   MT.ID, MT.ADDRESS, SQ.maxdate
FROM MyTable MT
INNER JOIN (SELECT ID,MAX(DATEFIELD) AS maxdate FROM  MyTable  GROUP BY ID ) SQ
ON SQ.ID = MT.ID
AND SQ.maxdate = MT.DateField

I have also created SQL Fiddle example for the same.

Hope this will help.

---

Glad to help! Please remember to accept the answer if you found it helpful. It will be useful for future readers having same issue.


My Profile on Microsoft ASP.NET forum