How to return the last ID of a query

Navigation
2

I'm working with an Oracle database

I have two tables:

Student Table: 

IDALUNO
IDMATRICULA
FASE

Test Table: 

IDTESTE
IDALUNO
IDMATRICULA
TESTE
STATUS

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

View Students Inquiry 

IDALUNO
IDMATRICULA
NOME_ALUNO
ESCOLA

In summary, each Student can have more than one Test , and I have to build a SELECT that returns me a Test of each Student , if there is more than one Test , I will only return the last Test entered in the system.

There is the function MAX so I'm not sure how it would apply to my function ... My query looks 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
    
asked by anonymous 27.01.2015 / 18:44

3 answers

3

Since the last test is the highest ID, the MAX function goes into 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)
    
27.01.2015 / 19:12
1

I did not test in ORACLE but MySQL 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
    
27.01.2015 / 20:22
1

Try this: 

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 case I used the analytic function MAX , with the GROUP BY clause. In case, it does not return the last test, it returns the highest test for each student. But by your explanation as the test is a sequence, then getting MAX already works.

    
10.03.2015 / 20:52
Revert a string and possibility of method addition on native objects in Python Remote validation preventing data editing