-2

I have 2 tables. Student and test. I would like to make a query which gets the student name (firstname and lastname) of the student which got the highest value in "test.test_results". I have tried 2 queries but both do not give me the output I want:

 SELECT student.studentFirstName, student.studentLastName, test.test_results,  FROM student INNER JOIN test ON student.studentID = test.studentID where MAX(test.test_results) ;

This Query is wrong i think and gives me an error.

   SELECT student.studentFirstName, student.studentLastName, test.test_results, MAX(test.test_results)  FROM student INNER JOIN test ON student.studentID = test.studentID;

This Query runs and gives me the student who got the highest result however it creates an extra column named "MAX(test.test_result)"

How can I make a query which gives me the name of the student who got the highest test_results from the table test.

CsharpStudent
  • 61
  • 8

1 Answers1

0

You can use a subquery to find the highest test result, and then select a row from student based on that value.

SELECT student.studentFirstName, student.studentLastName, test.test_results FROM student INNER JOIN test ON student.studentID = test.studentID WHERE test.test_results = (SELECT MAX(test_results) FROM test)

Here you SELECT MAX(test_result) FROM test and then you select the student that is associated with that value.

WereGoingOcean
  • 98
  • 1
  • 10