CODEWITHSUNDEEP
sql
Josh
Josh

Reputation: 11

SQL query to list the highest grade

I don't know how to write a query to display all students that have taken the same course more than once. Also list the students highest grade for the course. I wrote this query but I still don't see highest letter grade.

He wants it to look like this:

enter image description here

Input:

TNumber     Course_id   Attempts    Highest grade 
00012345    CSC2110     2           D 
00015366    CSC2110     3           B

My query:

select tnumber, courseid, count(*)
from GRADES
group by tnumber, courseid
having count(*) > 1

Upvotes: 1

Views: 4133

Answers (4)

am2
am2

Reputation: 371

if you need not only the highest result by Student and course but also the highest result by course, you could use (ORACLE- Syntax):

WITH INPUT AS (
  SELECT '123' TNUMBER, 'AA' COURSEID, 'D' GRADE FROM DUAL UNION ALL
  SELECT '123' TNUMBER, 'AA' COURSEID, 'B' GRADE FROM DUAL UNION ALL
  SELECT '123' TNUMBER, 'AA' COURSEID, 'C' GRADE FROM DUAL UNION ALL
  SELECT '234' TNUMBER, 'AA' COURSEID, 'A' GRADE FROM DUAL UNION ALL
  SELECT '234' TNUMBER, 'AA' COURSEID, 'B' GRADE FROM DUAL UNION ALL
  SELECT '123' TNUMBER, 'BB' COURSEID, 'C' GRADE FROM DUAL UNION ALL  
  SELECT '234' TNUMBER, 'BB' COURSEID, 'C' GRADE FROM DUAL UNION ALL  
  SELECT '234' TNUMBER, 'BB' COURSEID, 'D' GRADE FROM DUAL
)
SELECT 
  I1.TNUMBER, 
  I1.COURSEID, 
  COUNT(*), 
  MIN(I1.GRADE), 
  MIN(I2.HIGHEST_GRADE)
FROM INPUT I1
JOIN (SELECT COURSEID, MIN(GRADE) HIGHEST_GRADE FROM INPUT GROUP BY COURSEID) I2
ON I1.COURSEID = I2.COURSEID
GROUP BY I1.TNUMBER, I1.COURSEID
HAVING COUNT(*)>1
;

The with- clause is only to create the data

Results would be

TNUMBER COURSEID    COUNT(*)    MIN(I1.GRADE)   MIN(I2.HIGHEST_GRADE)
123 AA  3   B   A
234 AA  2   A   A
234 BB  2   C   C

234 BB 2 C C

Upvotes: 0

am2
am2

Reputation: 371

it is depending, what is the "highest" grade. If 'A' is higher than 'B', you must use min instead of max, because string sorting gives 'A' < 'B'

select tnumber, courseid, count(*), min(grade) as HIGHEST_GRADE
from GRADES 
group by tnumber, courseid 
having count(*)>1

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93704

Just add max(grade) to your current query to get the maximum grade for each student in each course 

select tnumber, courseid, count(*),max(grade) 
from GRADES 
group by tnumber, courseid 
having count(*)>1

Upvotes: 0

Teja
Teja

Reputation: 13534

SELECT tnumber, MAX( grade ) AS highest_grade
  FROM GRADES
 WHERE courseid = '123'
 GROUP BY tnumber;

Upvotes: 1

Related Questions