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

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

Hay varias formas de solucionar este problema. Puede usar 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; 

Consulte SQL Fiddle con demostración

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

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; 

Consulte SQL Fiddle con demostración

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

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

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 Demo

Comentarios

• Gracias, encuentro la unión interna a una tabla de subconsultas (ejemplo 1 arriba) para que sea la más intuitiva … y no ' no me obliga a aprender qué partición por es todo sobre. 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 unión interna (seleccione max (dateField) como newestDate, siteID del grupo originalTable por siteID) como newTable en oT.siteID = newTable.site_ID y oT.dateField = newTable.newestDate order by oT.siteID asc Para mí, eso explica mejor qué ' s sucede en la subconsulta.

I «Me sorprende que nadie haya aprovechado las funciones de la ventana más allá de row_number ()

Aquí hay algunos datos para jugar:

CREATE TABLE SCHOOL_STAFF ( LAST_UPDATE_DATE_TIME VARCHAR(20), SCHOOL_CODE VARCHAR(20), PERSON_ID VARCHAR(20), STAFF_TYPE_NAME VARCHAR(20) ); INSERT INTO SCHOOL_STAFF VALUES ("24-JAN-13", "ABE", "111222", "Principal"); INSERT INTO SCHOOL_STAFF VALUES ("09-FEB-12", "ABE", "222111", "Principal"); 

El La cláusula OVER () crea una ventana para la que definirá sus grupos agregados. En este caso, solo estoy particionando en SHOOL_CODE, por lo que veremos el FIRST_VALUE, que vendrá del LAST_UPDATE_DATE_TIME, agrupado por SCHOOL_CODE, y en el orden de LAST_UPDATE_DATE_TIME en orden descendente. Este valor se aplicará a toda la columna de cada SCHOOL_CODE.

Es importante prestar mucha atención a su partición y ordenación en la cláusula over ().

SELECT DISTINCT FIRST_VALUE(LAST_UPDATE_DATE_TIME) OVER (PARTITION BY SCHOOL_CODE ORDER BY LAST_UPDATE_DATE_TIME DESC) AS LAST_UPDATE ,FIRST_VALUE(SCHOOL_CODE) OVER (PARTITION BY SCHOOL_CODE ORDER BY LAST_UPDATE_DATE_TIME DESC) AS SCHOOL_CODE ,FIRST_VALUE(PERSON_ID) OVER (PARTITION BY SCHOOL_CODE ORDER BY LAST_UPDATE_DATE_TIME DESC) AS PERSON_ID FROM SCHOOL_STAFF WHERE STAFF_TYPE_NAME = "Principal" ORDER BY SCHOOL_CODE 

Devuelve:

24-JAN-13 ABE 111222 

Esto debería eliminar su necesidad de GROUP BY y subconsultas en la mayor parte. Sin embargo, querrá asegurarse de incluir DISTINCT.

Comentarios

• Esto es bueno, pero hay alguna manera de evitar repetir la cláusula over para todas las columnas?

select LAST_UPDATE_DATE_TIME as LAST_UPDATE, SCHOOL_CODE, PERSON_ID from SCHOOL_STAFF WHERE STAFF_TYPE_NAME="Principal" AND LAST_UPDATE_DATE_TIME = (SELECT MAX(LAST_UPDATE_DATE_TIME) FROM SCHOOL_STAFF s2 WHERE PERSON_ID = s2.PERSON_ID) 

Comentarios

• En lugar de publicar solo código, debe intentar explicar cómo esto responde a la pregunta; y potencialmente lo que el OP estaba haciendo incorrectamente.