How to select sql max date?

277 Asked by AndreaBailey in SQL Server , Asked on Oct 6, 2022

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 the latest date.

Answered by Andrea Bailey

To select sql max date -

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)

Share

How to select sql max date?

Your Answer