Inner Join Database

Question

Staff I am using this command on sqlserver:

SELECT ALUNOS.NOME, DISCIPLINAS.NOME AS DISCIPLINA, MAX(NOTAS.NOTA) AS MEDIA FROM NOTAS
INNER JOIN ALUNO_DISCIPLINA ON ALUNO_DISCIPLINA.identificacao = NOTAS.codAlunoDisciplina
INNER JOIN DISCIPLINAS ON ALUNO_DISCIPLINA.CODIGO_DISCIPLINA = DISCIPLINAS.CODIGO
INNER JOIN ALUNOS ON ALUNO_DISCIPLINA.registro_aluno = ALUNOS.Registro
GROUP BY DISCIPLINAS.NOME,ALUNOS.NOME
ORDER BY DISCIPLINAS.NOME

Then the result generates the following table according to the image

I would like you to show only (MARIA Geography 9.00) and (JOÃO Portugues 10.00), which are the highest grades of the students, that is to compare between Portuguese and show the highest note tbm. Any suggestions where I’m going wrong??

Answer 1

I can’t test here, but I believe what you need is a subquery, SELECT FROM SELECT, test there and tell me if it works (you may have to make some adjustment):

SELECT NOME, DISCIPLINA, MAX(MEDIA) AS MEDIA FROM 
    (SELECT ALUNOS.NOME, DISCIPLINAS.NOME AS DISCIPLINA, MAX(NOTAS.NOTA) AS MEDIA FROM
        NOTAS
        INNER JOIN ALUNO_DISCIPLINA ON ALUNO_DISCIPLINA.identificacao = NOTAS.codAlunoDisciplina
        INNER JOIN DISCIPLINAS ON ALUNO_DISCIPLINA.CODIGO_DISCIPLINA = DISCIPLINAS.CODIGO
        INNER JOIN ALUNOS ON ALUNO_DISCIPLINA.registro_aluno = ALUNOS.Registro
        GROUP BY DISCIPLINAS.NOME,ALUNOS.NOME
        ORDER BY DISCIPLINAS.NOME) 
GROUP BY DISCIPLINA

Answer 2

To be able to do what you need, you can use the function RANK in this way:

WITH resumo AS (SELECT
                    A.Nome NomeAluno,
                    D.Nome NomeDisciplina,
                    MAX(N.Nota) MaiorNota,
                    RANK() OVER (PARTITION BY D.Nome ORDER BY MAX(N.Nota) DESC)  Posicao
                FROM ALUNOS A
                    INNER JOIN ALUNO_DISCIPLINA AD ON (A.Registro = AD.registro_aluno)
                    INNER JOIN DISCIPLINAS D ON (AD.CODIGO_DISCIPLINA = D.CODIGO)
                    INNER JOIN NOTAS N ON (AD.identificacao = N.codAlunoDisciplina)
                GROUP BY
                    A.Nome,
                    D.Nome)

SELECT 
    r.NomeAluno,
    r.NomeDisciplina,
    r.MaiorNota
FROM 
    resumo r 
WHERE 
    r.Posicao = 1
ORDER BY
    r.NomeDisciplina,
    r.NomeAluno;

The PARTITION BY divides the data by the name of the discipline and the ORDER BY DESC orders from highest grade to lowest grade, so if there are same grades for different students in the same subject, all students will appear. The WITH serves for you can name a grudge and use it then as I did.

References:

RANK (Transact-SQL)
WITH common_table_expression (Transact-SQL)