Hi @Mansoor Mohammed ,
Welcome to Microsoft Q&A!
Please refer below two methods and check whether they are working:
--DDL and sample data
drop table if exists Student,SchoolStudents,StudentReport
create table Student
(StudentName varchar(10),
StudentDOB date,
Class int)
insert into Student values
('John', '1/1/2000', 1),
('Sam', '2/2/2000', 2),
('Ram' ,'3/3/2000', 3)
create table SchoolStudents
(StudentName varchar(10),
StudentDOB date,
Class int,
ExamAttempt int,
Result int)
insert into SchoolStudents values
('John', '1/1/2000', 1,1,50),
('Sam', '2/2/2000', 2,3,60),
('John', '1/1/2000', 1,2,70),
('Ram' ,'3/3/2000', 3,4,80),
('John', '1/1/2000', 1,5,90)
create table StudentReport
(StudentName varchar(10),
Result int)
--Method one
; WITH numbering AS (
SELECT StudentName, StudentDOB, ExamAttempt,Result,class,
row_number() OVER (PARTITION BY StudentName,StudentDOB,class ORDER BY ExamAttempt DESC) AS rowno
FROM SchoolStudents
)
INSERT StudentReport(StudentName, Result)
SELECT n.StudentName, n.result
FROM numbering n
JOIN Student s ON n.StudentName = s.StudentName
AND s.StudentDOB = n.StudentDOB AND S.Class=N.Class
WHERE n.rowno = 1
--Method Two
INSERT StudentReport(StudentName, Result)
select distinct a.StudentName,a.Result
from SchoolStudents a
inner join Student b on a.Class=b.Class and a.StudentName=b.StudentName and a.StudentDOB=b.StudentDOB
inner join (select StudentName,max(ExamAttempt) over (PARTITION BY StudentName,StudentDOB,class) max from SchoolStudents ) c
on a.StudentName=b.StudentName and a.ExamAttempt=c.max
--Query final result
select * from StudentReport
Output:
StudentName Result
John 90
Ram 80
Sam 60
Best regards
Melissa
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.