I have three tables. Marks, Students and Subjects
Marks
RowID StudentID SubjectID Mark
-------------------- ----------- ----------- -----------
1 1 1 50
2 1 2 60
3 1 3 70
4 1 4 100
5 1 5 45
6 1 6 75
7 2 1 55
8 2 2 75
9 2 3 65
10 2 4 100
11 2 5 88
12 2 6 95
13 3 1 40
14 3 2 32
15 3 3 20
16 3 4 33
17 3 5 50
18 3 6 47
19 4 1 100
20 4 2 95
21 4 3 70
22 4 4 65
23 4 5 85
24 4 6 73
25 5 1 55
26 5 2 65
27 5 3 75
28 5 4 85
29 5 5 57
30 5 6 67
31 6 1 75
32 6 2 80
33 6 3 90
34 6 4 55
35 6 5 54
36 6 6 53
37 7 1 44
38 7 2 65
39 7 3 66
40 7 4 77
41 7 5 88
42 7 6 52
43 8 1 30
44 8 2 70
45 8 3 75
46 8 4 45
47 8 5 33
48 8 6 66
49 9 1 55
50 9 2 66
51 9 3 77
52 9 4 88
53 9 5 65
54 9 6 79
Students
StudentID RegNo Name Sex DOB Address
1 S1 Anderson Male 1996-01-01 00:00:00.000 72 Dewhurst Road, Cheshunt,Herts,EN8 9P
2 S2 Russell Male 1996-02-01 00:00:00.000 2 Wolsey Avenue,Cheshunt,Herts,EN7 5QB
3 S3 Phillip Male 1996-01-28 00:00:00.000 28 Second Avenue,Galley Hill,Waltham Abbey,Essex,EN9 2AW
4 S4 Paul Male 1996-03-02 00:00:00.000 30 Second Avenue,Galley Hill,Waltham Abbey,Essex,EN9 2AW
5 S5 Jaime Female 1996-04-04 00:00:00.000 10 Davies Street,Hertford,Herts,SG13 7BU
6 S6 Angela Female 1996-06-03 00:00:00.000 47 Cameron Drive,Waltham Cross,Herts,EN8
7 S7 Reena Female 1996-07-09 00:00:00.000 11 Bromleigh Close,Cheshunt,Herts,EN8 0RN
8 S8 Manu Male 1996-06-02 00:00:00.000 1 Blackthorn Close,Diss,Norfolk,IP22 4ZA
9 S9 Reeja Female 1996-07-02 00:00:00.000 4 Tregelles Road,Hoddesdon,Herts,EN11 9H
Subjects
SubjectID Subject MaxTotal
1 English 100
2 Hindi 100
3 Malayalam 100
4 Physics 100
5 Chemistry 100
6 Maths 100
I want to get result in a new table Columns needed in the resultant table : Subject, Top Scorer (Mark), Reg.No
"Any one please help me to attain this result"
Submit an article or tip