Distinct Record with MAX Value

Some time you require to get record from parent and child table on basis of maximum of on some column base. So below is the code with two tables one parent and other is child. Here we need to get the single record having max start and end date.

Copy and paste the code in SQL Query window and execute

/* Parent Table */

DECLARE @Property      AS TABLE (Id INT IDENTITY(1, 1) PRIMARY KEY, Refs VARCHAR(100))

/* Child Table */

DECLARE @PropertyInfo  AS TABLE (

            Id INT IDENTITY(1, 1) PRIMARY KEY,

            PId INT, /* Asume it as Foreign Key to Id column of Property Table as its not possible to create refrence is not allowed */

            StartDate DATE,

            EndDate DATE

        )

 INSERT INTO @Property VALUES ('Abc'), ('Def'), ('Ghi'), ('Jkl')

 INSERT INTO @PropertyInfo(PId, StartDate, EndDate)

 VALUES  (1, '2013/10/10', '2013/10/15'), (1, '2013/11/10', '2013/11/15'), (1, '2013/12/10', '2013/12/15'),

             (2, '2013/10/10', '2013/10/15'), (2, '2013/12/10', '2013/12/16'),

             (3, '2013/12/10', '2013/12/15'), (3, '2013/12/15', '2014/01/16'),

             (4, '2013/11/10', '2013/11/15'), (4, '2014/01/15', '2014/03/16')

 /* Select all records */

 SELECT a.*, b.StartDate, b.EndDate FROM @Property a, @PropertyInfo b

 WHERE a.Id = b.PId

 /* Select with max date only, single record for each PId */

 SELECT b.Id, b.Refs, a.StartDate, a.EndDate FROM

 (

SELECT ROW_NUMBER() OVER (PARTITION BY(PId) ORDER BY(EndDate) DESC) R,      PId, StartDate, EndDate

       FROM @PropertyInfo

 ) a, @Property b

 WHERE a.R = 1

 AND a.Pid = b.Id