Select unique records from multiple records based on max date

Your current query is not giving the desired result because you are using a GROUP BY clause on the PERSON_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 each 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;

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:

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;

See SQL Fiddle with Demo

This query implements row_number() which assigns a unique number to each row in the partition of SCHOOL_CODE and placed in a descending order based on the LAST_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 the row_number() will only return one. If you want to return both with a windowing function, then consider using the rank() windowing function instead as it will return ties:

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;

See Demo

Your query can be simplified to:

SELECT
    c.id,
    c.name,
    a.rtime,
    s.status_name,
    u.user_name
FROM company c
    LEFT JOIN
      ( SELECT 
            company_id,
            MAX(rtime) AS maxdate
        FROM action
        WHERE deleted IS NULL
        GROUP BY company_id
      ) AS x ON x.company_id = c.id
    LEFT JOIN action a ON  a.deleted IS NULL
                       AND a.company_id = x.company_id 
                       AND a.rtime = x.maxdate 
    LEFT JOIN user u ON u.id = a.user_id
    LEFT JOIN status s ON s.id = a.status_id
WHERE 
    c.name LIKE 'Company%' ;

An index on (deleted, company_id, rtime) would make the derived table subquery efficient. I suppose you already have indexes on the columns used for the joins and on the Company (name).

SQL-Fiddle