You can do it using CTE also.

--========= Student table

IF OBJECT_ID('tempdb..#Student') IS NOT NULL

BEGIN

DROP TABLE #Student

END

GO

CREATE TABLE #Student

(

StudID VARCHAR(100),

RollNo VARCHAR(100)

)

GO

INSERT INTO #Student VALUES('Alpha', 'D1234')

INSERT INTO #Student VALUES('beta', 'A1122')

INSERT INTO #Student VALUES('charlie', 'D1234')

INSERT INTO #Student VALUES('bravo', 'C1342')

INSERT INTO #Student VALUES('tom', 'B1964')

INSERT INTO #Student VALUES('harry', 'A1122')

Go

--========= StudentSubjects table

IF OBJECT_ID('tempdb..#StudentSubjects') IS NOT NULL

BEGIN

DROP TABLE #StudentSubjects

END

GO

CREATE TABLE #StudentSubjects

(

RollNo VARCHAR(100),

SubjectCode VARCHAR(100),

)

GO

INSERT INTO #StudentSubjects VALUES ('D1234', 1001)

INSERT INTO #StudentSubjects VALUES ('A1122', 4001)

INSERT INTO #StudentSubjects VALUES ('D1234', 2001)

INSERT INTO #StudentSubjects VALUES ('C1342', 5001)

INSERT INTO #StudentSubjects VALUES ('B1964', 6001)

INSERT INTO #StudentSubjects VALUES ('A1122', 3001)

Go

--======= Solution:

;WITH MyCTE (RollNo, LowestSubjectCode)

AS

(

SELECT RollNo, MIN (SubjectCode) AS LowestSubjectCode FROM #StudentSubjects GROUP BY RollNo

)

SELECTS.StudId,S.RollNo,MC.LowestSubjectCode

FROM#Student S INNER JOIN MyCTE MC

ONS.RollNo = MC.RollNo