Results 1 to 2 of 2

Thread: Adding columns of data in an aggregate query

  1. #1
    Tim Heald Guest

    Adding columns of data in an aggregate query


    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

  2. #2
    Chris H Guest

    Adding columns of data in an aggregate query (reply)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •