Author |
Topic |
Adura19
Starting Member
5 Posts |
Posted - 2013-01-05 : 14:15:03
|
I have 3 tables; student,subject & score.I want to do the position of each student base on each subject, e.g student with 1st position in Eng, Math etc kindly help. Thanks |
|
shenulal
Starting Member
11 Posts |
Posted - 2013-01-06 : 03:13:56
|
Create table #student(ID int identity(1,1), StudentName varchar(50))Create table #subject (ID int identity(1,1), Subject varchar(50))Create table #score(ID int identity(1,1), SubjectID int, StudentID int, Score decimal(18,2))Insert into #student(StudentName) Select 'Shenu' Union Select 'Prasad' Union Select 'Shihab' Union Select 'KP' union Select 'Biju' UnionSelect 'Shihab' Union Select 'Shanweel' Union Select 'Sufana' Union Select 'Niya' union Select 'Raju'Insert into #subject(Subject) Select 'English' Union Select 'Maths' Union Select 'Physics' Insert into #score(SubjectID,StudentID,Score) Select 1,1, 40.5 UNION Select 2,1, 48 Union Select 3,1, 44 union Select 1,2, 38.5 UNION Select 2,2, 43 Union Select 3,2, 49 union Select 1,3, 42.5 UNION Select 2,3, 45 Union Select 3,3, 47 union Select 1,4, 42.25 UNION Select 2,4, 45.25 Union Select 3,4, 46 union Select 1,5, 25 UNION Select 2,5, 27 Union Select 3,5, 39 union Select 1,6, 34 UNION Select 2,6, 33 Union Select 3,6, 38 union Select 1,7, 44 UNION Select 2,7, 49 Union Select 3,7, 48.5 union Select 1,8, 47 UNION Select 2,8, 45.25 Union Select 3,8, 49.25 union Select 1,9, 41.25 UNION Select 2,9, 47.5 Union Select 3,9, 49.5 select S.Score,SS.Subject,#student.StudentNamefrom ( select #subject.ID as SubjectID,#subject.Subject,max(#score.Score) as MaxScore from #score inner join #subject on #subject.ID= #score.SubjectID group by #subject.ID,#subject.Subject) as SS inner join #score as S on SS.SubjectID = S.SubjectID and SS.MaxScore=S.Score inner join #student on #student.ID=S.StudentID; |
|
|
Adura19
Starting Member
5 Posts |
Posted - 2013-01-06 : 09:51:36
|
Thanks for ur help.But d query only generated student with highest score in each subject.What i want is student with 1st position,2nd position, 3rd position and so on in each subject.Thanks. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-06 : 10:08:48
|
[code]SELECT *,DENSE_RANK() OVER (PARTITION BY subject ORDER BY score DESC) AS SubjectPositionFROM table[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Adura19
Starting Member
5 Posts |
Posted - 2013-01-06 : 16:09:05
|
Thank you all for contributions. It works perfectly. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-07 : 10:02:43
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|