For example, I have 3 records in first table and 2 records in second table then the mapping should be as follows

As there is no third record in second table, nulls should be displayed.

In order to implement this, I tried following code:

Based on above code, I am able to achieve the output. Let me know whether this is a proper approach or not.

EDIT: I'm sure I confused everyone with the MarksId, RankId and LastRank. Let us consider a new example,

My goal is to assign the 1st sport to the student who enrolled first and second sport to the second student, and for third student there will be no sport.

For example, I have 3 records in the first table and 2 records in the second table.

The mapping should be as follows:

As there is no third record in the second table, nulls should be displayed.

In order to implement this, I've tried the following code:

Based on the above code, I am able to achieve the output. Let me know whether this is a proper approach or not.

Here's a new example:

My goal is to assign the 1^st sport to the student who enrolled in the first and second sport to the second student, and for the third student there will be no sport.

EDIT: I'm sure I confused everyone with the MarksId, RankId and LastRank. Let us consider a new example,

In this case, there are three students enrolled for sports, you can check the order in which they enrolled. But there are only two vacant sports available and the order in which they have to be selected.

Now using my query, which will be

declare @Students table (StudentId int, StudentName varchar(50), EnrolledOn datetime)
declare @Sports table (SportId int, SportName varchar(50))

insert into @Students values (1, 'John', '2013-05-25 10:00:00')
insert into @Students values (2, 'Jerry', '2013-05-25 14:30:00')
insert into @Students values (3, 'Jane', '2013-05-26 10:30:00')

insert into @Sports values (1, 'Golf')
insert into @Sports values (2, 'Foot ball')

;with Students (RowNumber, StudentId, StudentName, EnrolledOn) as
(
    select row_number() over(order by EnrolledOn) as RowNumber, * from @Students
)
,Sports (RowNumber, SportId, SportName) as
(
    select row_number() over(order by SportId) as RowNumber, * from @Sports
)
select s1.StudentId, s1.StudentName, s1.EnrolledOn, s2.SportId, s2.SportName from Students s1
left join Sports s2
on s1.RowNumber = s2.RowNumber

I'm getting the expected output.

My goal is to assign the 1st sport to the student who enrolled first and second sport to the second student, and for third student there will be no sport.

Will this be proper now ?