Query oracle group

Question

Query oracle group

Navigation

#1 by (2 votes)

0

I have the following tables:

Students

id   nombre
 1   Lucas

Exams

id  fecha       nota  alumno_id
 1  25/11/2018   5       1
 2  26/11/2018   8       1

I need with a query, to extract the last note of the student according to his date, I have tried:

select alumno_ID , MAX(fecha) from examenes 
where alumno_ID = (select id from alumnos) 
GROUP BY alumno_ID ;

This brings me a record but I need tb the id of the exam table, what I need is to extract from all students what is their last exam according to the date in the exam table

sql oracle

asked by sirdaiz 26.11.2018 в 14:01

source

1 answer

Are the data of the variables or objects initialized in the constructor method stored in RAM directly? How can I navigate this JSON

score 2 · Accepted Answer

To solve the query, you only have to gather your current result, returned with the table, uniting it by date and student.

Then you would have something like this

select a.alumno_ID, a.max_fecha, b.id 
from (
    select alumno_ID , MAX(fecha) max_fecha
    from examenes 
    where alumno_ID = (select id from alumnos) 
    GROUP BY alumno_ID) a inner join examenes b
        on a.max_fecha= b.fecha and a.alumno_ID = b.alumno_ID

Now, I keep insisting that where does not make any sense anywhere.

I would try the following query, which will bring you exactly the same result.

select a.alumno_ID, a.max_fecha, b.id 
    from (
        select alumno_ID , MAX(fecha) max_fecha
        from examenes 
        GROUP BY alumno_ID) a inner join examenes b
            on a.max_fecha= b.fecha and a.alumno_ID = b.alumno_ID

What's more, if you think about it, that query only works if the student table has only one record, because you can not match a table's data to the response of another select if it has more than one record (you can do it with an IN).