Obtain a set of data for each user

Thanks friends to all who helped me and I managed to get the query until here I managed to arrive so it may not be very good but it works, and they only adapt it to their needs:

    SELECT nombre, apellido_pa, apellido_ma, nombre_carrera, 
    resultado, puntajes.id_perfil, nombre_perfil FROM perfil, 
    carrera,alumno, puntajes WHERE puntajes.id_perfil = perfil.id_perfil 
    and alumno.id_carrera = carrera.id_carrera and 
alumno.id_alumno = puntajes.id_alumno and puntajes.resultado >= IFNULL((SELECT resultado FROM 
puntajes AS p2 WHERE p2.id_alumno = puntajes.id_alumno ORDER BY resultado DESC LIMIT 1 OFFSET 2), 0) ORDER BY puntajes.id_alumno, resultado DESC

You can add a GROUP BY student ID combined with the highest result to obtain student data. I have assumed the relations with the student and career tables.

SELECT nombre_carrera, puntajes.id_alumno, nombre,apellido_pa, apellido_ma,  MAX(resultado)
FROM puntajes p
INNER JOIN alumno a ON a.id_alumno = p.id_alumno
INNER JOIN carrera c ON c.id_carrera = a.id_carrera
GROUP BY puntajes.id_alumno 
ORDER BY resultado DESC 
LIMIT 3;

I leave an example implemented with a single table because I do not know the organization of the other tables.

This is the table that you use as an example:

The query is as follows:

SELECT A.id, MAX(nota) AS nota1,
(SELECT AL.nota FROM alumnos AS AL WHERE AL.id = A.id AND AL.nota <> (SELECT nota FROM alumnos AS AL2 WHERE AL2.id = AL.id ORDER BY nota DESC LIMIT 1)
ORDER BY AL.nota DESC LIMIT 1) AS nota2,
(SELECT AL.nota FROM alumnos AS AL WHERE AL.id = A.id AND AL.nota <> (SELECT nota FROM alumnos AS AL2 WHERE AL2.id = AL.id ORDER BY nota DESC LIMIT 1) AND AL.nota <> ((SELECT AL.nota FROM alumnos AS AL WHERE AL.id = A.id AND AL.nota <> (SELECT nota FROM alumnos AS AL2 WHERE AL2.id = AL.id ORDER BY nota DESC LIMIT 1)
ORDER BY AL.nota DESC LIMIT 1))
ORDER BY AL.nota DESC LIMIT 1) AS nota3
FROM alumnos AS A
GROUP BY A.id

The result is the following:

The alternative if you use a version of MariaDB more updated or MySQL Server itself would be:

SELECT A.id, MAX(nota) AS nota1,
(SELECT AL.nota FROM alumnos AS AL WHERE AL.id = A.id AND AL.nota <> (SELECT nota FROM alumnos AS AL2 WHERE AL2.id = AL.id ORDER BY nota DESC LIMIT 1)
ORDER BY AL.nota DESC LIMIT 1) AS nota2,
(SELECT AL.nota FROM alumnos AS AL WHERE AL.id = A.id AND AL.nota NOT IN (SELECT nota FROM alumnos AS AL2 WHERE AL2.id = AL.id ORDER BY AL2.nota DESC LIMIT 2)
ORDER BY AL.nota DESC LIMIT 1) AS nota3
FROM alumnos AS A
GROUP BY A.id

The problem is that:

  

This version of MariaDB does not support 'LIMIT & IN / ALL / ANY / SOME   subquery '

If we go to real life this would be easier with an auxiliary language PHP, Java, etc. Or even within the same MySQL using a stored procedure, any power query can be performed but sometimes it is very cumbersome to solve it only with MySQL.

To implement the previous code, you will only have to perform the corresponding JOINS where necessary. I hope it helps you.