MYSQL Query does not work, is there a simpler query for this case?

I have a STUDENT table which contains data about students from a faculty:

STUDENT(ID, Name, Grade, ID_Tutor)

Structure and data:

CREATE TABLE STUDENT
    (`ID` int, `Name` varchar(5), `Grade` int, `ID_Tutor` int);

INSERT INTO STUDENT
    (`ID`, `Name`, `Grade`, `ID_Tutor`)
VALUES
    (1,    'A',     10,       NULL),
    (2,    'B',     9.5,      1),
    (3,    'C',     9,        1),
    (4,    'D',     8,        1),
    (5,    'E',     7,        4),
    (6,    'F',     8,        1),
    (7,    'G',     5,        7),
    (10,   'H',     6,        5)
    (11,   'I',     7,        3),
    (12,   'J',     9,        10),
    (13,   'K',     9.6,      11),
    (14,   'L',     8.9,      5),
    (15,   'M',     9.9,      13),
    (16,   'N',     7,        2),
    (17,   'O',     7.9,      2),
    (18,   'P',     2,        15);

Top 5 faculty grades:

    `ID`, `Name`, `Grade`, `ID_Tutor`
    (1,    'A',     10,       NULL),
    (15,   'M',     9.9,      13),
    (13,   'K',     9.6,      11),
    (2,    'B',     9.5,      1),
    (3,    'C',     9,        1),
    (12,   'J',     9,        10),

In result should appear students with these tutors from the above table.

I want to make a SQL query (MYSQL) to find

• student name,
• tutor name and
• tutor grade

for students whose tutor has a grade in top 5 faculty students (and without using LIMIT and/or ROWNUM - for a Oracle solution).

Result should look like this:

| student name | tutor name | tutor grade |
       'B'           'A'          10
       'C'           'A'          10
       'D'           'A'          10
       'F'           'A'          10
       'P'           'M'           9.9
       'M'           'K'           9.6
       'N'           'B'           9.5
       'O'           'B'           9.5
       'I'           'C'           9

I tried something like this (but not working) and I think I'm complicating a lot... (and I should not use ROWNUM)

SELECT
    s.ID,
    s.Name Student,
    s.Grade,
    p.ID_Tutor,
    p.Grade
FROM
    STUDENT s,
    (SELECT * FROM
        (SELECT * FROM
            (SELECT
                t.Name Tutor ,
                t.ID ,
                Grade Grade_Tutor
            FROM STUDENT s
                JOIN STUDENT t
                    on s.ID_Tutor = t.ID
            GROUP BY t.Name, t.ID)
        ORDER BY 3 desc )
    WHERE ROWNUM < 6) p
WHERE s.ID_Tutor = p.ID

Give me this error:

    #1248 - Every derived table must have its own alias

Thanks in advance!

---

Your are lacking of several table alias on your code. Check this working SQL Fiddle code. But it does not return any value as I just added some dumb values. Feel free to use it and test your results.

SELECT s.ID, s.Name Student, s.Grade, p.ID_Tutor, p.Grade_tutor
FROM STUDENT s,
    (SELECT * FROM
        (SELECT * FROM
            (SELECT t.Name Tutor, t.ID , s.Grade Grade_Tutor, s.ID_Tutor
               FROM STUDENT s
               JOIN STUDENT t
                    on s.ID_Tutor = t.ID
            GROUP BY t.Name, t.ID) innerTable
         ORDER BY 3 desc ) secondInnerTable
     WHERE @ROWNUM < 6) p
WHERE s.ID_Tutor = p.ID