Seleccione cuál tiene fecha máxima o última fecha

  • Aquí hay dos tablas.

    PERSONAL DE LA ESCUELA

    SCHOOL_CODE + STAFF_TYPE_NAME + LAST_UPDATE_DATE_TIME + PERSON_ID
    =================================================================
    ABE           Principal         24-JAN-13               111222
    ABE           Principal         09-FEB-12               222111
    

    PERSONAS

    PERSON_ID + NAME
    =================
    111222      ABC
    222111      XYZ
    

    Aquí está mi consulta de Oracle.

    SELECT MAX(LAST_UPDATE_DATE_TIME) AS LAST_UPDATE, SCHOOL_CODE, PERSON_ID
    FROM SCHOOL_STAFF
    WHERE STAFF_TYPE_NAME='Principal'
    GROUP BY SCHOOL_CODE, PERSON_ID
    ORDER BY SCHOOL_CODE;
    

    que da este resultado

    LAST_UPDATE SCHOOL_CODE PERSON_ID
    ===========+===========+=========
    24-JAN-13   ABE         111222
    09-FEB-12   ABE         222111
    

    Quiero seleccionar el primero para la escuela que tiene la última fecha.

    Gracias.

  • Taryn

    Taryn Respuesta correcta

    hace 7 años

    Su consulta actual no da el resultado deseado porque está utilizando un GROUP BY cláusula sobre el PERSON_ID columna que tiene un valor único para ambas entradas. Como resultado, devolverá ambas filas.

    Hay algunas formas de solucionar este problema. Puede utilizar una subconsulta para aplicar la función agregada para devolver el max(LAST_UPDATE_DATE_TIME) para cada SCHOOL_CODE:

    select s1.LAST_UPDATE_DATE_TIME,
      s1.SCHOOL_CODE,
      s1.PERSON_ID
    from SCHOOL_STAFF s1
    inner join
    (
      select max(LAST_UPDATE_DATE_TIME) LAST_UPDATE_DATE_TIME,
        SCHOOL_CODE
      from SCHOOL_STAFF
      group by SCHOOL_CODE
    ) s2
      on s1.SCHOOL_CODE = s2.SCHOOL_CODE
      and s1.LAST_UPDATE_DATE_TIME = s2.LAST_UPDATE_DATE_TIME;
    

    Ver Violín SQL con demostración

    O puede usar un función de ventana para devolver las filas de datos de cada escuela con los datos más recientes LAST_UPDATE_DATE_TIME:

    select SCHOOL_CODE, PERSON_ID, LAST_UPDATE_DATE_TIME
    from
    (
      select SCHOOL_CODE, PERSON_ID, LAST_UPDATE_DATE_TIME,
        row_number() over(partition by SCHOOL_CODE 
                            order by LAST_UPDATE_DATE_TIME desc) seq
      from SCHOOL_STAFF
      where STAFF_TYPE_NAME='Principal'
    ) d
    where seq = 1;
    

    Ver Violín SQL con demostración

    Esta consulta implementa numero de fila() que asigna un número único a cada fila en la partición de SCHOOL_CODE y colocados en orden descendente según el LAST_UPDATE_DATE_TIME.

    Como nota al margen, la función JOIN con agregación no es exactamente la misma que la row_number() versión. Si tiene dos filas con el mismo tiempo de evento, JOIN devolverá ambas filas, mientras que row_number() solo devolverá uno. Si desea devolver ambos con una función de ventana, considere usar el rank() función de ventana en su lugar, ya que devolverá lazos:

    select SCHOOL_CODE, PERSON_ID, LAST_UPDATE_DATE_TIME
    from
    (
      select SCHOOL_CODE, PERSON_ID, LAST_UPDATE_DATE_TIME,
        rank() over(partition by SCHOOL_CODE 
                            order by LAST_UPDATE_DATE_TIME desc) seq
      from SCHOOL_STAFF
      where STAFF_TYPE_NAME='Principal'
    ) d
    where seq = 1;
    

    Ver Manifestación

    Gracias, encuentro que la unión interna a una tabla de subconsultas (ejemplo 1 arriba) es la más intuitiva ... y no requiere que aprenda de qué se trata _partición por_. A continuación, se muestra una sintaxis similar al ejemplo 1: seleccione oT.dateField, oT.siteID, oT.field1, oT.field2, oT.field3, de ** originalTable ** como ** oT ** unir internamente (seleccione max (dateField) como newestDate, siteID de originalTable agrupar por siteID ) como tabla nueva en oT.siteID = newTable.site_ID y oT.dateField = newTable.newestDate ordenar por oT.siteID asc Para mí, eso explica mejor lo que está sucediendo en la subconsulta.

Licencia bajo CC-BY-SA con atribución


Contenido fechado antes 26/06/2020 9:53