hi, i need to Retrieve data about students who receive the biggest stipend of their faculty; include faculty where
they study. i have this :
SELECT
ROWNUM AS Nr,
FACULTY_NAME,
NAME,
SURNAME,
STIPEND
FROM (
SELECT
faculty.faculty_name AS FACULTY_NAME,
student.name AS NAME,
student.surname AS SURNAME,
MAX(a.stipend) AS STIPEND
FROM
faculty,
student, (
SELECT
student.id_student,
MAX(money.stipend) AS stipend
FROM
student,
money
WHERE
student.id_student = money.student_id
GROUP BY student.id_student
HAVING MAX(money.stipend) >0
) a
WHERE
faculty.id_faculty = student.faculty_id AND
student.id_student = a.id_student
GROUP BY faculty.faculty_name, student.name, student.surname
);
I don't know why it shows all Stipends, i need only MAX stipend from Faculty.
1st picture is what i receive from my query. 2nd picture of all tables. Please help and tell me where i have a mistake
