Adding columns of data in an aggregate query

This requires a correlated sub-query. The logic is: "Find the lowest grade achieved by a student in class 2. Then find all the students in class 2 which achieved that grade".

I've replaced the table join in the WHERE clause with an ANSI-SQL join. Joining in the WHERE clause will still work, but this is a non-standard join and there is no guarantee that future SQL Server releases will still support it (though they probably will). I'm not sure of the need for ABS(), so have left it in. Note that it if you are using the data returned in a program, this query may return more than one row (if 2 or more students got the same lowest grade).

There are other ways this could have been achieved, but this is probably the easiest to understand.

Happy SQL!

Chris H

select g.stu_id, g.grade
from grade g inner join class c on (abs(c.stu_id) = abs(g.stu_id))
where g.grade = (select min(gr.grade)
from dbo.class cl inner join dbo.grade gr on (abs(cl.stu_id) = abs(gr.stu_id))
where cl.class = 2)

------------
Tim Heald at 3/14/01 9:36:12 AM

Ok so I need to write a query that finds the lowest grade out of a group of students (by a class number), and identifies it by a student id #.

Here's what I'm trying:

select min(gr.grade), gr.stu_id
from dbo.class cl, dbo.grade gr
where (cl.class = 2) and (abs(cl.stu_id) = abs(gr.stu_id))
group by gr.stu_id

but unfortunetly it returns the lowest grade for each student in class 2, and not the single lowest grade in class 2 and the stu_id (student ID).

I'm new to SQL and could really use the help.

Thanks