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