How to return the last query ID

Asked

Viewed 633 times

2

I’m working with an Oracle bank

I have two tables:

Table Student:

IDALUNO
IDMATRICULA
FASE

Test Table:

IDTESTE
IDALUNO
IDMATRICULA
TESTE
STATUS

I also have a view that gives me the information of the students

View Students Consultation

IDALUNO
IDMATRICULA
NOME_ALUNO
ESCOLA

In summary, each Pupil may have more than one Testing, and I have to build a SELECT that returns me a Testing of each Pupil, if there is more than one Testing, I will return only the last Testing embedded in the system.

There is the function MAX I’m just not sure how I’d apply myself to this job... My query is like this:

SELECT IDTESTE, IDALUNO, IDMATRICULA, CA.NOME_ALUNO, CA.ESCOLA FROM ALUNO MA 
INNER JOIN VW_CONSULTA_ALUNOS CA ON CA.IDALUNO = MA.IDALUNO
INNER JOIN TESTE MT ON MT.IDALUNO = MA.IDALUNO AND MT.IDMATRICULA = MA.IDMATRICULA
sql oracle

3 answers

3


Whereas the last test is the highest ID, the MAX function goes in a subquery to return this higher ID. The subquery, in turn, is filtered by the student returned by the main query.

SELECT IDTESTE, IDALUNO, IDMATRICULA, CA.NOME_ALUNO, CA.ESCOLA FROM ALUNO MA 
INNER JOIN VW_CONSULTA_ALUNOS CA ON CA.IDALUNO = MA.IDALUNO
INNER JOIN TESTE MT ON MT.IDALUNO = MA.IDALUNO AND MT.IDMATRICULA = MA.IDMATRICULA
WHERE IDTESTE = (SELECT MAX(IDTESTE) FROM TESTE WHERE IDALUNO = MA.IDALUNO)

  • I can do this within a subquery from an Inner Join?

  • @Marcelobonifazio I didn’t understand the question. Did the query work? Do you want it to look different?

  • 1

    Gave straight @Caffé, I had tried to do another way, only there would involve group by and would end up leaving a gambiarra, this way was much more efficient

1

Try it this way:

SELECT MAX(idteste) ultimo_teste
      ,idaluno
      ,idmatricula
      ,ca.nome_aluno
      ,ca.escola
  FROM aluno ma
 INNER JOIN vw_consulta_alunos ca
    ON ca.idaluno = ma.idaluno
 INNER JOIN teste mt
    ON mt.idaluno = ma.idaluno
   AND mt.idmatricula = ma.idmatricula
 GROUP BY idaluno
         ,idmatricula
         ,ca.nome_aluno
         ,ca.escola;

In this case I used the analytical function MAX, with the clause GROUP BY. In this case, it does not return the last test, it returns the highest test for each student. But for your explanation of how the test is a sequel, so taking the MAX already works.

1

I have not tested in ORACLE but in Mysql it works.

SELECT MAX(IDALUNO), IDMATRICULA, CA.NOME_ALUNO, CA.ESCOLA FROM ALUNO MA 
INNER JOIN VW_CONSULTA_ALUNOS CA ON CA.IDALUNO = MA.IDALUNO
INNER JOIN TESTE MT ON MT.IDALUNO = MA.IDALUNO AND MT.IDMATRICULA = MA.IDMATRICULA
GROUP BY CA.NOME_ALUNO

Browser other questions tagged sql oracle

You are not signed in. Login or sign up in order to post.