Home » SQL & PL/SQL » SQL & PL/SQL » query
query [message #232000] Thu, 19 April 2007 01:14 Go to next message
webadministrator
Messages: 45
Registered: October 2005
Member
Hello,
I need to do the following query:

i have 20 courses (taking code_course) and 50 students (code_stu) i want to know the student with the higher grade (code_gra) in each course. i know i should use group by grade, having but im not able to do it!

thank you
Re: query [message #232006 is a reply to message #232000] Thu, 19 April 2007 01:23 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
can u try this

select code_course,max(code_gra) from <table_name>  group by code_course,code_stu



--Yash
Re: query [message #232008 is a reply to message #232000] Thu, 19 April 2007 01:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post what you did and we'll guide you.
Don't forget to always post your Oracle version.

Regards
Michel
Re: query [message #232014 is a reply to message #232000] Thu, 19 April 2007 01:40 Go to previous messageGo to next message
webadministrator
Messages: 45
Registered: October 2005
Member
select code_course, code_student
from grade
where code_grade in ( select code_grade from course, grade
where grade.code_course = course.code_course
and code_course in ( 361,
583,
625,
688,
819) group by code_grade having code_grade = max(code_grade)


oracle version: oracle 8.0
Re: query [message #232016 is a reply to message #232014] Thu, 19 April 2007 01:47 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I forgot to mention you to format your post: read "How to format your posts".

You have to get in a subquery what is the max grade for each course and then in outer query select the students that have this grade for this course.

Regards
Michel
Previous Topic: NEAREST and DATE (yet again merged)
Next Topic: Function help
Goto Forum:
  


Current Time: Sun Dec 11 00:13:12 CST 2016

Total time taken to generate the page: 0.08748 seconds