Click here to Skip to main content
15,040,212 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i have one situation
one table is STUDENT_CRpt columns
row_id(identity),
Stu_Id(int),
Stu_Name(varchar),
Class(varchar),
Subject(varchar),
Marks(int)


second table is STUDENT_DTL_Crpt columns:
Stu_Id(int),
Class(varchar),
Gender(varchar),
Address(varchar)


Now in STUDENT_CRpt table 6 rows (raw data)
1   raam    V     Maths      56
2   Sham    V     Maths      59
3   Sham    V     English    49
4   raam    V     English    56
1   geeta   VI    English    60
1   geeta   VI    science    60


According to this in english highest marks are 56 and in maths 59 of V class and of vi class 60

so i tried following query to get output like
1   raam    V   Maths      56   59
2   Sham    V   Maths      59   59
3   Sham    V   English    49   56
4   raam    V   English    56   56
1   geeta   VI   English   60   60
1   geeta   VI   science   60   60
here last column of each row (which is bold) is "highest marks in respective subject of respective class

i hope u got my scenario........
so i used below query

select s.Stu_Name,s.Class,s.Marks,s.Subject ,d.Gender,d.Address,
(select MAX(s.Marks) as 'Highest Marks' from STUDENT_CRpt  group by s.Subject,s.Class)


 from STUDENT_CRpt s,STUDENT_DTL_Crpt d where s.Stu_ID=d.Stu_ID and s.Class=d.Class group by s.Stu_Name,s.Class,d.Address,d.Gender,s.Subject,s.Marks order by Class


i need to tell highest marks too with respective class and subject in each row



but getting an error

Each GROUP BY expression must contain at least one column that is not an outer reference.

i did somewhere wrong ..do tell so that i can get out of this

regards,
thanks
Posted
Updated 20-Apr-13 2:17am
v6
Comments
Dnyaneshwar Kondbale 18-Apr-13 6:57am
   
Hey you want list all students marks with their class ,subject and adress,gender.
Or you want Highest mark of student.
Or You want Class,subject,Highest Mark,and Student name and address whose get highest mark
Please clear If I am wrong.
shivani 2013 18-Apr-13 7:08am
   
i Have 2 classes V and VI with respective 2 subjects english,maths and maths,science.now i want to list all students name with their respective marks opbtained,highest marks(respective class and subject),class,subject,gender and address.
i hope u got it
Dnyaneshwar Kondbale 18-Apr-13 7:29am
   
Try this .. I thing its help you..

from STUDENT_CRpt s,STUDENT_DTL_Crpt d where s.Stu_ID=d.Stu_ID and s.Class=d.Class
group by s.Stu_Name,s.Class,d.Address,d.Gender,s.Subject,s.Marks order by Class
select s.Stu_Name,s.Class,s.Marks,s.Subject ,d.Gender,d.Address, M.Marks as 'Highest Marks'
from STUDENT_CRpt s,STUDENT_DTL_Crpt d,(select MAX(Marks) as Marks,Subject,Class,Sty_Name from STUDENT_CRpt group by Subject,Class,Sty_Name)
M where s.Stu_ID=d.Stu_ID and s.Class=d.Class and m.Subject=s.Subject and s.Class=m.Class and s.Sty_Name=m.Sty_Name
shivani 2013 18-Apr-13 8:58am
   
it doesn't worked thanks for ur effort
shivani 2013 20-Apr-13 3:48am
   
i have updated my question......do see

1 solution

Try this:
SQL
SELECT s.Stu_ID, s.Stu_Name, s.Class, MAX(s.Marks) AS 'Highest marks', s.Subject, d.Gender, d.Address
FROM STUDENT_CRpt AS s INNER JOIN  STUDENT_DTL_Crpt AS d ON s.Stu_ID=d.Stu_ID and s.Class=d.Class
GROUP BY s.Stu_ID, s.Stu_Name, s.Class, s.Subject, d.Gender,d.Address 
ORDER BY s.Class, s.Stu_Name


[EDIT #1]
Thank you for explanation ;)


To get higest marks in class for subject:
SQL
SELECT s.Class, s.Subject, MAX(s.Marks) AS 'Highest marks', 
FROM STUDENT_CRpt AS s
GROUP BY s.Class, s.Subject


To get highest marks for each student in class for subject:
SQL
SELECT s.Stu_ID, s.Stu_Name, s.Class, s.Subject, MAX(s.Marks) AS 'Highest marks'
FROM STUDENT_CRpt AS s
GROUP BY s.Stu_ID, s.Stu_Name, s.Class, s.Subject


To get highest marks for each student for subject and for class:
SQL
SELECT t1.*, t2.[Highest marks] AS 'Highest in Class'
FROM (
        --get all students with their marks
	SELECT s.Stu_ID, s.Stu_Name, s.Class, s.Subject, MAX(s.Marks) AS 'Highest marks'
	FROM STUDENT_CRpt AS s
	GROUP BY s.Stu_ID, s.Stu_Name, s.Class, s.Subject
	) AS t1 LEFT JOIN (
                --get highest marks in class
		SELECT s.Class, s.Subject, MAX(s.Marks) AS 'Highest marks', 
		FROM STUDENT_CRpt AS s
		GROUP BY s.Class, s.Subject
		) AS t2 ON t1.Class = t2.Class AND t1.Subject = t2.Subject


Note: Not tested, because i haven't access to SQL Server at this moment.
I hope it help.
[/EDIT]
   
v3
Comments
Sridhar Patnayak 18-Apr-13 7:03am
   
Nice 5+
Maciej Los 18-Apr-13 7:05am
   
Thank you ;)
shivani 2013 18-Apr-13 7:18am
   
my scenario is :there are 2 students of V class having eng subject each getting respective marks 55 and 54.now when i list i should have one more column highest marks which will be shown in each row (here 55) .so in both rows it should show 55 marks of english subject with other detials(like student name,class.marks obtained) and same for maths subject of same student and the of different class student...i hope u got my scenario........
Maciej Los 18-Apr-13 7:23am
   
Not really...
Please, improve question (use "Improve question" widget) and provide the structure of tables and example data, and expected output.
shivani 2013 20-Apr-13 3:48am
   
i have updated my question......do see
Maciej Los 20-Apr-13 6:27am
   
You did not explain anything ;(
As i wrote before, i need to see the structure of your tables and example data... and expected output.
shivani 2013 20-Apr-13 7:57am
   
pls see to it .i have updated it ........
Maciej Los 20-Apr-13 8:34am
   
OK, thank you.
See my answer after update ;)
shivani 2013 22-Apr-13 7:13am
   
Thank you so much.very helpful.really appreciable....even got the way to ask the question and thanks for updating the question...never thought of derived tables to work on..
Maciej Los 23-Apr-13 1:33am
   
You're welcome ;)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900