Here are two tables.
SCHOOL_STAFF
SCHOOL_CODE + STAFF_TYPE_NAME + LAST_UPDATE_DATE_TIME + PERSON_ID
=================================================================
ABE Principal 24-JAN-13 111222
ABE Principal 09-FEB-12 222111
PERSONS
PERSON_ID + NAME
=================
111222 ABC
222111 XYZ
Here is my oracle query.
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;
which gives this results
LAST_UPDATE SCHOOL_CODE PERSON_ID
===========+===========+=========
24-JAN-13 ABE 111222
09-FEB-12 ABE 222111
I want to select the first one for the school which has latest date.
Thanks.
Best Answer
Your current query is not giving the desired result because you are using a
GROUP BY
clause on thePERSON_ID
column which has a unique value for both entries. As a result you will return both rows.There are a few ways that you can solve this. You can use a subquery to apply the aggregate function to return the
max(LAST_UPDATE_DATE_TIME)
for eachSCHOOL_CODE
:See SQL Fiddle with Demo
Or you can use use a windowing function to return the rows of data for each school with the most recent
LAST_UPDATE_DATE_TIME
:See SQL Fiddle with Demo
This query implements
row_number()
which assigns a unique number to each row in the partition ofSCHOOL_CODE
and placed in a descending order based on theLAST_UPDATE_DATE_TIME
.As a side note, the JOIN with aggregate function is not exactly the same as the
row_number()
version. If you have two rows with the same event time the JOIN will return both rows, while therow_number()
will only return one. If you want to return both with a windowing function, then consider using therank()
windowing function instead as it will return ties:See Demo