sql query

[rosh]

Hi,
I have a student table with stdno,stdname,stdmajor,stdgpa etc as columns.
I need to create a Sql statement to find : For each major ----the stdno,name and gpa of student with highest gpa.

The following stmt gives error,pls help
SELECT StdMajor,StdNo,StdFirstName,StdLastName,StdGPA
WHERE EXISTS
(Select StdMajor,MAX(stdgpa) from student
GROUP BY Stdmajor)
GROUP BY Stdmajor,StdNo,StdFirstName,StdLastName,StdGPA

Rosh

 

[Access Hero]

This is totally off the top of my head, untested, etc. It is your job to verify it works, troubleshoot, squash bugs:

SELECT a.StdMajor,a.StdNo,a.StdFirstName,a.StdLastName,a.StdGPA 
from
student a inner join 
(Select StdMajor,MAX(StdGPA ) as maxgpa from student
GROUP BY Stdmajor) b on
a. StdMajor = b.StdMajor AND a.StdGPA = b.maxgpa

That's one way to do it.

 

[ajetrumpet]

i wonder if this would work:

SELECT tbl.StdMajor, tbl.StdNo, tbl.StdFirstName, tbl.StdLastName, tbl.StdGPA
   FROM tbl WHERE tbl.StdGPA IN 
      (SELECT MAX(tbl.StdGPA) AS StdGPA FROM tbl GROUP BY tbl.StdMajor);

 

[rosh]

hi accesshero,

Your query works perfectly but i want to do it using subquery.
Can you give some idea ?

 

[Access Hero]

i wonder if this would work:

SELECT tbl.StdMajor, tbl.StdNo, tbl.StdFirstName, tbl.StdLastName, tbl.StdGPA
   FROM tbl WHERE tbl.StdGPA IN 
      (SELECT MAX(tbl.StdGPA) AS StdGPA FROM tbl GROUP BY tbl.StdMajor);

Adam,

That will only work if the maximum GPA is unique to a given major.

 

[Access Hero]

hi accesshero,

Your query works perfectly but i want to do it using subquery.
Can you give some idea ?

The sample I submitted to you does contain a sub-query.