0

I have a Module and Students table as well as a linker table called Results. I am trying to display only one record of the student with the highest result for the specific module but i cannot get it to work. Here is my sql syntax so far:

SELECT S.ST_FNAME, S.ST_LNAME, M.MOD_NAME, R.RES_GRADE
FROM STUDENT S, MODULE M, RESULTS R
WHERE S.ST_ID=R.ST_ID AND M.MOD_ID=R.MOD_ID
AND M.MOD_ID=503;

At the moment it displays all the students and their results for the specific module.

help would be appreciated

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
user3112130
  • 21
  • 7

1 Answers1

1

To get a single value for a group of rows, use GROUP BY (in your case, with MAX to get the maximum):

SELECT 
  S.ST_FNAME, 
  S.ST_LNAME, 
  M.MOD_NAME, 
  max(R.RES_GRADE) as max_grade
FROM STUDENT S
JOIN RESULTS R on S.ST_ID = R.ST_ID
JOIN MODULE M on R.MOD_ID = M.MOD_ID
WHERE M.MOD_ID=503
GROUP BY s.st_fname, s.st_lname, m.mod_name;

I've also converted your old-style joins to ANSI-style joins - using ANSI style is preferred by most people nowadays.

SQL Fiddle

Frank Schmitt
  • 30,195
  • 12
  • 73
  • 107
  • s.ST_ID = 503 and m.MOD_ID= 503 are two different things – vav Apr 28 '14 at 18:45
  • i assume you meant to write m.mod_id=503 instead of s.st_id. I've changed it and it still shows me all 3 results, instead of one top result that i want. any advice? – user3112130 Apr 28 '14 at 18:52
  • @user3112130 You may want to make sure that there is no duplicate data in your tables. SELECT st_fname, st_lname from student group by st_fname, st_lname having count(*) > 1; And again, SELECT mod_name from MODULE group by mod_name having count(*) > 1; – Joseph B Apr 28 '14 at 19:03
  • @user3112130 You're right about the mod_id, of course. Fixed. If you still don't get the correct results, please add your example data to your question, as well as the output you're expecting. – Frank Schmitt Apr 28 '14 at 20:56