SQL Get rows based on max column

Question

I have problem getting row from a max column, here the illustration : http://sqlfiddle.com/#!3/15207/9

basically I want to get the student's highest grade along with the grade-id (gid). but the query is not right because of using max grade id which will return 2.

First course taken, the student got D (1 point). Second time course taken, the student got E (0 point).

The gradeid for that 1 point is 1.

I expect the query result is: 1 (userid), 1 (subjectid), 1 (gradeid), 1 (score)

Thanks

UPDATE 01:

The more improved example: http://sqlfiddle.com/#!3/97997/2

John Woo · Accepted Answer · 2013-03-28 12:05:44Z

3

UPDATE 1

WITH highestScore
AS
(
    SELECT  a.uid StudentID,
            a.uname StudentName,
            b.Score, 
            c.cname CourseName,
            c.semester,
            d.sname SubjectName, d.sid, 
            DENSE_RANK() OVER (PARTITION BY a.uid, d.sid
                               ORDER BY b.Score DESC) rn
    FROM    Users a
            INNER JOIN Grades b
                ON a.uid = b.uid
            INNER JOIN Courses c
                ON b.cid = c.cid
            INNER JOIN Subjects d
                ON c.sid = d.sid
)
SELECT  StudentID, StudentName, CourseName, semester,
        SubjectName, Score
FROM    highestScore
WHERE   StudentID = 1 AND  -- StudentID
        sid = 1 AND        -- SubjectID
        RN = 1             -- leave this as is (rank of the highest score)

SQLFiddle Demo

edited Mar 28, 2013 at 12:05

answered Mar 28, 2013 at 10:42

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Bonn Over a year ago

It failed when I added another record in this example sqlfiddle.com/#!3/97997/2 (expected result is 1,1,1,1) Sorry I didn't add more rows in previous example since the students will have a lot of different subjects and courses

John Woo Over a year ago

let me see the example :D

John Woo Over a year ago

see my updated answer. it uses common table expression and window function

John Woo Over a year ago

see my updated answer or if you still more interested on the ID, see here sqlfiddle.com/#!3/97997/17

Collectives™ on Stack Overflow

SQL Get rows based on max column

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related