| Author |
 Topic  |
|
|
Adura19
Starting Member
5 Posts |
 Posted - 01/05/2013 : 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
India
11 Posts |
Posted - 01/06/2013 : 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' Union
Select '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.StudentName
from (
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 - 01/06/2013 : 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
India
47189 Posts |
Posted - 01/06/2013 : 10:08:48
|
SELECT *,DENSE_RANK() OVER (PARTITION BY subject ORDER BY score DESC) AS SubjectPosition
FROM table
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Adura19
Starting Member
5 Posts |
Posted - 01/06/2013 : 16:09:05
|
Thank you all for contributions.
It works perfectly. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47189 Posts |
Posted - 01/07/2013 : 10:02:43
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
Ranking
