Home / Questions / 299927
Accepted
Juan Pinzón
Asked: 2020-10-11 08:59:03 +0800 CST 2020-10-11 08:59:03 +0800 CST

Get last record saved in a table with relation 1 to N

  • 772

I have the following tables:

EMPLOYEE

ID |NOMBRE | APELLIDO 
1  |TEST   | TEST     
2  |TEST2  | TEST2
3  |TEST3  | TEST3
4  |TEST4  | TEST4

DIRECTION

ID | DIRECCION | EMP_ID | PAIS_ID |FECHA_INSERCION
1  | DIR1      | 1      | 1       |10/10/2019 08:00:00
2  | DIR2      | 1      | 1       |10/10/2019 08:15:00
3  | DIR3      | 1      | 5       |10/10/2019 08:20:00

I want to get the last registered address of the employee

for instance

TEST   | TEST  | DIR1 | 5 | 10/10/2019 08:20:00

I have tried the following query

Select Nombre,Apellido, Pais_ID, DIRECCION, MAX(FECHA_INSERCION)
FROM EMPLEADO EMP
INNER JOIN DIRECCION DIR ON EMP.ID = DIR.EMP_ID
GROUP BY Nombre, Apellido, Pais_ID, DIRECCION

However this gets me the following:

TEST   | TEST  | DIR1 | 1 | 10/10/2019 08:15:00
TEST   | TEST  | DIR1 | 5 | 10/10/2019 08:20:00

How could I fix the query to get what I need?

sql

2 Answers

  1. Best Answer

    It is not entirely clear to me if you are looking for a particular case or the last addresses of all employees, if it is the latter, you can pivot with a subquery that gives you the last date for each employee:

    SELECT  EMP_ID,
        MAX(FECHA_INSERCION) as 'ULTIMA_FECHA_INSERCION'
    FROM DIRECCION DIR 
    GROUP BY EMP_ID

    You incorporate this into your own query to filter only the rows where FECHA_INSERCION = ULTIMA_FECHA_INSERCION. Something like that:

    SELECT  EMP.Nombre, 
        EMP.Apellido, 
        DIR.Pais_ID, 
        DIR.DIRECCION, 
        DIR.FECHA_INSERCION
    FROM EMPLEADO EMP
    INNER JOIN DIRECCION DIR 
        ON EMP.ID = DIR.EMP_ID
    INNER JOIN (SELECT  EMP_ID,
                MAX(FECHA_INSERCION) as 'ULTIMA_FECHA_INSERCION'
                FROM DIRECCION
                GROUP BY EMP_ID
        ) U
        ON U.EMP_ID = EMP.ID
        AND U.ULTIMA_FECHA_INSERCION = DIR.FECHA_INSERCION

    On the contrary, if you are looking for a single case, the natural way would be to sort by FECHA_INSERCIONdescending and keep the first of the rows. Unfortunately, Oracle in versions prior to 12c does not have a row limitation clause like other managers, so the trick is to materialize a row number according to the order in a subquery and filter the first

    SELECT  *
    FROM (Select Nombre, 
                 Apellido, 
                 Pais_ID, 
                 DIRECCION, 
                 FECHA_INSERCION,
                 ROWNUM RN
            FROM EMPLEADO EMP
            INNER JOIN DIRECCION DIR 
                ON EMP.ID = DIR.EMP_ID
            WHERE EMP.ID = 1
            ORDER BY FECHA_INSERCION DESC
    ) T
    WHERE T.RN = 1

    Another similar way to the previous one is to use the function ROW_NUMBER()to number

    ROW_NUMBER() OVER (PARTITION BY EMP.ID ORDER BY DIR.FECHA_INSERCION)  RN

    To generate the row number, the advantage is that you generate a unique numerator for each employee, so ultimately, you could solve the query for everyone, using the same filter:WHERE T.RN = 1

    Grades:

    • The rownumis resolved after the orderafter the wheretherefore it is necessary to use a subquery to materialize this row number
    • Clarification with the first query, this one does not avoid the problem of rows with the same date for the same employee, in that case it will retrieve all the matching ones.
    • 2

  2. You would need to add a TOP 1so that it returns only one value (in Oracle it is with rownum) and also add a ORDER BYso that it orders them from highest to lowest date, that way the top 1 will always be the FECHA_INSERCIÓNhighest:

    Select Nombre,Apellido, Pais_ID, DIRECCION, MAX(FECHA_INSERCION)
    FROM EMPLEADO EMP
    INNER JOIN DIRECCION DIR ON EMP.ID = DIR.EMP_ID
    GROUP BY Nombre, Apellido, Pais_ID, DIRECCION
    ORDER BY MAX(FECHA_INSERCION) DESC
where rownum = 1
    • 1