--===== TEST DATA =========
declare @Person table
 (Id int, Name varchar(20), MartialStatus int, EmploymentStatus int, Email varchar(50), JobId int );
 
INSERT INTO @Person
 ( Id, Name, MartialStatus, EmploymentStatus, Email, JobId )
VALUES
 (1, 'John', 8, 6, 'John@xxx.com', 99);
INSERT INTO @Person
 ( Id, Name, MartialStatus, EmploymentStatus, Email, JobId )
VALUES
 (2, 'John', 10, 7, 'John@xxx.com', 100);
INSERT INTO @Person
 ( Id, Name, MartialStatus, EmploymentStatus, Email, JobId )
VALUES
 (3, 'John', NULL, NULL, 'John@xxx.com', 101);
INSERT INTO @Person
 ( Id, Name, MartialStatus, EmploymentStatus, Email, JobId )
VALUES
 (4, 'Max', 6, 5, 'max@emailreaction.org', 102);
 
--select * from @Person 

declare @Job table
 (JobId int, GroupId int, [Desc] varchar(30) );
 
INSERT INTO @Job
 ( JobId, GroupId, [Desc] )
VALUES
 (99, 50, 'blah blah');
INSERT INTO @Job
 ( JobId, GroupId, [Desc] )
VALUES
 (100, 50, 'blah blah');
INSERT INTO @Job
 ( JobId, GroupId, [Desc] )
VALUES
 (101, 50, 'blah blah');
INSERT INTO @Job
 ( JobId, GroupId, [Desc] )
VALUES
 (102, 51, 'blah blah');
 
--select * from @Job 

--I want to retrieve a record from the person table based on the group id... 
--for eg. for Group Id 50 I want the following record which is mix of record 3 and 2 as:
--------------------------------------
--3, John,10,7,John@xxx.com,101
---------------------------------------

--Record num 3 should be retrieved with the marital status and employment status from record two as they were null.

--====== SUGGESTED SOLUTION =========
declare @GroupId int;
set @GroupId = 50;

with CTE as
(
select 
	RowId = ROW_NUMBER() over (partition by p.Name order by p.JobId desc),
	p.Id, 
	p.Name, 
	p.MartialStatus, 
	p.EmploymentStatus, 
	p.Email, 
	p.JobId
from @job j
	inner join @Person p on j.jobId = p.JobId
where j.GroupId = @GroupId
)
select a.Id,
	a.Name, 
	MartialStatus = coalesce(a.MartialStatus,b.MartialStatus,c.MartialStatus), 
	EmploymentStatus = coalesce(a.EmploymentStatus,b.EmploymentStatus,c.EmploymentStatus), 
	a.Email, 
	a.JobId
from CTE a
	left outer join CTE b on a.RowId +1 = b.RowId
	left outer join CTE c on a.RowId +2 = c.RowId
where a.RowId = 1;

declare @Person table
 (Id int IDENTITY, Name varchar(20), MartialStatus int
    ,EmploymentStatus int, Email varchar(50), JobId int );
 
INSERT INTO @Person ( Name, MartialStatus, EmploymentStatus, Email, JobId )
SELECT 'John', 8, 6, 'John@xxx.com', 99
UNION ALL SELECT 'John', 10, 7, 'John@xxx.com', 100
UNION ALL SELECT 'John', NULL, NULL, 'John@xxx.com', 101
UNION ALL SELECT 'Max', 6, 5, 'max@emailreaction.org', 102
UNION ALL SELECT 'Dwain', NULL, 5, 'dwain@ssc.com', 103
UNION ALL SELECT 'Dwain', 6, 2, 'dwain@ssc.com', 104
UNION ALL SELECT 'Dwain', NULL, 10, 'dwain@yahoo.com', 105
 
--select * from @Person 

declare @Job table (JobId int, GroupId int, [Desc] varchar(30) );
 
INSERT INTO @Job ( JobId, GroupId, [Desc] )
SELECT 99, 50, 'blah blah'
UNION ALL SELECT 100, 50, 'blah blah'
UNION ALL SELECT 101, 50, 'blah blah'
UNION ALL SELECT 102, 51, 'blah blah'
UNION ALL SELECT 103, 52, 'blah blah'
UNION ALL SELECT 104, 52, 'blah blah'
UNION ALL SELECT 105, 52, 'blah blah'

;WITH Grouper AS (
        SELECT Id, Name, MartialStatus, EmploymentStatus, Email, a.JobId, GroupId
            ,n=ROW_NUMBER() OVER (PARTITION BY GroupID ORDER BY Id DESC)
        FROM @Person a
        INNER JOIN @Job b ON a.JobId = b.JobId
        ),
    PickLast AS (
        SELECT Id, Name, MartialStatus, EmploymentStatus, Email, JobId, GroupId, n
        FROM Grouper
        WHERE n = 1
        UNION ALL
        SELECT a.Id, a.Name
            ,ISNULL(b.MartialStatus, a.MartialStatus)
            ,ISNULL(b.EmploymentStatus, a.EmploymentStatus)
            ,ISNULL(b.Email, a.Email)
            ,a.JobId, a.GroupId, a.n
        FROM Grouper a
        INNER JOIN PickLast b ON a.GroupID = b.GroupID AND 
            a.n = b.n + 1
        WHERE b.MartialStatus IS NULL OR b.EmploymentStatus IS NULL OR b.Email IS NULL
        )
SELECT Id, Name, MartialStatus, EmploymentStatus, Email, JobId, GroupId
FROM (
    SELECT Id, Name, MartialStatus, EmploymentStatus, Email, JobId, GroupId
        ,n=ROW_NUMBER() OVER (PARTITION BY GroupId ORDER BY n DESC) 
    FROM PickLast) a
WHERE n = 1