sql, function in sql

Question

DELIMITER $
CREATE FUNCTION MAXSCOREID(START_STUDENT_ID INT, END_STUDENT_ID INT)
RETURNS INT
BEGIN
DECLARE STUDENT_ID INT;
BEGIN
    SELECT MAX(SCORE) INTO STUDENT_ID 
    FROM STUDENT ST, SCORE S 
    WHERE ST.STUDENT_ID = S.STUDENT_ID
    AND ST.STUDENT_ID BETWEEN 1 AND 10;
END;
RETURN student_id;
END$
DELIMITER ;

Hi. With the function above, I get the higher score but I want to get the student`s id who has the max score. Any help, please?

Also you're using inconsistent case on your variables, and not using your input parameters. — miken32
– miken32, Commented Jun 15, 2017 at 23:40

rd_nielsen · Accepted Answer · 2017-06-15 23:22:05Z

1

This query will produce the set of students who have the maximum score.

select student_id
from score
where score = (select max(score) from score);

answered Jun 15, 2017 at 23:22

rd_nielsen

2,4592 gold badges14 silver badges19 bronze badges

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

2 Comments

Joseph Ateş Over a year ago

It works itself but when I put it into the function, it returns null.

rd_nielsen Over a year ago

Your function is declared to return a single int (i.e., a single student), but there may be more than one student with the maximum score. You will have to change the function to return a set of students or otherwise modify your process to adapt to the possibility of having multiple students with the maximum score.

miken32 · Accepted Answer · 2017-06-15 23:58:26Z

0

You can replace the body of your function with the following:

SELECT ST.STUDENT_ID INTO student_id
FROM STUDENT ST
LEFT JOIN SCORE S USING (STUDENT_ID)
WHERE ST.STUDENT_ID BETWEEN 1 AND 10
HAVING MAX(SCORE);

This uses the aggregate form of the MAX() function. I'd recommend reworking your function to differentiate variable names and column names; I'd also suggest using lower case column names to make your code easier to read!

answered Jun 15, 2017 at 23:58

miken32

42.5k16 gold badges127 silver badges177 bronze badges

Collectives™ on Stack Overflow

sql, function in sql

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related