Selecteer welke maximale datum of laatste datum heeft

Uw huidige zoekopdracht geeft niet het gewenste resultaat omdat u een GROUP BY clausule in de PERSON_ID kolom die een unieke waarde heeft voor beide items. Als gevolg hiervan retourneert u beide rijen.

Er zijn een paar manieren waarop u dit kunt oplossen. U kunt een subquery gebruiken om de aggregatiefunctie toe te passen om de max(LAST_UPDATE_DATE_TIME) voor elke SCHOOL_CODE te retourneren:

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; 

Zie SQL-fiddle met demo

Of u kunt een vensterfunctie om de rijen met gegevens voor elke school te retourneren met de meest recente 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; 

Zie SQL-viool met demo

Deze query implementeert row_number() die een uniek nummer toewijst aan elke rij in de partitie van SCHOOL_CODE en in aflopende volgorde wordt geplaatst op basis van de LAST_UPDATE_DATE_TIME.

Even terzijde: de JOIN met aggregatiefunctie is niet precies hetzelfde als de row_number() -versie. Als je twee rijen hebt met dezelfde gebeurtenistijd, zal JOIN beide rijen retourneren, terwijl de row_number() er slechts één retourneert. Als je beide wilt retourneren met een vensterfunctie, overweeg dan om in plaats daarvan de rank() vensterfunctie te gebruiken, aangezien deze ties teruggeeft:

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; 

Zie Demo

Reacties

• Bedankt, ik vind de innerlijke join met een subquerytabel (voorbeeld 1 hierboven) om het meest intuïtief te zijn .. en vereist niet ' Ik moet leren wat partitioneren door allemaal is over. Hier is een syntaxis die vergelijkbaar is met voorbeeld 1: selecteer oT.dateField, oT.siteID, oT.field1, oT.field2, oT.field3, van originalTable als oT inner join (selecteer max (dateField) als newestDate, siteID van originalTable group by siteID) als newTable op oT.siteID = newTable.site_ID en oT.dateField = newTable.newestDate order by oT.siteID asc Voor mij verklaart dat beter wat ' s gebeurt in de subquery.

I “Het verbaast me dat niemand gebruik heeft gemaakt van vensterfuncties buiten row_number ()

Hier zijn enkele gegevens om mee te spelen:

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

De OVER () clausule creëert een venster waarvoor u uw geaggregeerde groepen zult definiëren. In dit geval partitioneer ik alleen op de SHOOL_CODE, dus we zullen de FIRST_VALUE zien, die afkomstig is van LAST_UPDATE_DATE_TIME, gegroepeerd op SCHOOL_CODE, en in de volgorde LAST_UPDATE_DATE_TIME in aflopende volgorde. Deze waarde wordt toegepast op de hele kolom voor elke SCHOOL_CODE.

Het is belangrijk om goed te letten op uw partitionering en volgorde in de over () clausule.

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 

Retourneert:

24-JAN-13 ABE 111222 

Dit zou uw behoefte aan GROUP BY en subquerys moeten elimineren voor het grootste gedeelte. Je zult er echter zeker van willen zijn dat je DISTINCT opneemt.

Reacties

• Dit is leuk, maar er is een manier om te voorkomen dat je de over-clausule voor alle kolommen?

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) 

Reacties

• In plaats van alleen code te posten, zou je moeten proberen uit te leggen hoe dit de vraag beantwoordt; en mogelijk wat het OP onjuist deed.