Seleziona quale ha la data massima o la data più recente

La tua query corrente non sta dando il risultato desiderato perché stai utilizzando un GROUP BY nella colonna PERSON_ID che ha un valore univoco per entrambe le voci. Di conseguenza restituirai entrambe le righe.

Ci sono alcuni modi per risolvere questo problema. Puoi utilizzare una sottoquery per applicare la funzione di aggregazione per restituire max(LAST_UPDATE_DATE_TIME) per ogni 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; 

Vedi SQL Fiddle with Demo

Oppure puoi usare un funzione di finestre per restituire le righe di dati per ogni scuola con il 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; 

Vedi SQL Fiddle con demo

Questa query implementa row_number() che assegna un numero univoco a ciascuna riga nella partizione di SCHOOL_CODE e posizionata in ordine decrescente in base a LAST_UPDATE_DATE_TIME.

Come nota a margine, la funzione JOIN con aggregate non è esattamente la stessa della versione row_number(). Se hai due righe con la stessa ora dellevento, JOIN restituirà entrambe le righe, mentre row_number() ne restituirà solo una. Se desideri restituire entrambi con una funzione di windowing, considera lutilizzo della funzione di windowing rank() in quanto restituirà legami:

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; 

Vedi Demo

Commenti

• Grazie, trovo il join interno a una tabella di sottoquery (esempio 1 sopra) per essere il più intuitivo .. e ' non mi richiede di imparare cosè partizione per di. Ecco uno sguardo a una sintassi simile allesempio 1: seleziona oT.dateField, oT.siteID, oT.field1, oT.field2, oT.field3, da originalTable come oT inner join (seleziona max (dateField) come newestDate, siteID dal gruppo originalTable per siteID) come newTable su oT.siteID = newTable.site_ID e oT.dateField = newTable.newestDate order by oT.siteID asc Per me, questo spiega meglio cosa ' sta accadendo nella sottoquery.

I “Sono sorpreso che nessuno abbia sfruttato le funzioni della finestra oltre row_number ()

Ecco alcuni dati con cui giocare:

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"); 

Il La clausola OVER () crea una finestra per la quale definirai i tuoi gruppi aggregati. In questo caso, sto solo partizionando su SHOOL_CODE, quindi vedremo FIRST_VALUE, che verrà da LAST_UPDATE_DATE_TIME, raggruppato per SCHOOL_CODE e nellordine LAST_UPDATE_DATE_TIME in ordine decrescente. Questo valore verrà applicato allintera colonna per ogni SCHOOL_CODE.

È importante prestare molta attenzione al partizionamento e allordinamento nella clausola 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 

Restituisce:

24-JAN-13 ABE 111222 

Ciò dovrebbe eliminare la necessità di GROUP BY e sottoquery per la maggior parte. Tuttavia, assicurati di includere DISTINCT.

Commenti

• È carino, ma cè un modo per evitare di ripetere la clausola over per tutte le colonne?

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) 

Commenti

• Invece di pubblicare solo codice, dovresti cercare di spiegare come questo risponde alla domanda; e potenzialmente ciò che lOP stava facendo in modo errato.