SELECT
	ps.person_id,
	ps.person_name,
	(
	select 
		pa.person_reference as personid,
		pa.person_text as txt
	from 
		Person_Academia pa
	where 
		pa.type = 'USER' 
		AND pa.code = 'SPECIAL1'
		pa.person_reference = ps.person_reference
	order by
		pa.last_modified_date desc
	)
FROM
	Person_Staff ps

-- In oracle, yields "missing parenthesis" error.

1.  SELECT 
2.      ps.person_id, 
3.      ps.person_name, 
4.      ( 
5.      select 
6.          pa.person_reference as personid, 
7.          pa.person_text as txt 
8.      from 
9.          Person_Academia pa 
10.      where 
11.          pa.type = 'USER' 
12.          AND pa.code = 'SPECIAL1' 
13.          pa.person_reference = ps.person_reference 
14.      order by 
15.          pa.last_modified_date desc 
16.      ) 
17.  FROM 
18.      Person_Staff ps 
19.   
20.  -- In oracle, yields "missing parenthesis" error. 

SELECT
	ps.person_id,
	ps.person_name,
	(
		select txt from
		(
			select 
				pa.person_text as txt, 
				pa.person_reference as personid,
				row_number() over (order by pa.last_modified_date desc) r
			from 
				Person_Academia pa
			where 
				pa.type = 'USER' 
				AND pa.code = 'SPECIAL1'
		)
	        where personid = ps.person_id

	) as AcademicInfo
FROM
	Person_Staff ps

1.  SELECT 
2.      ps.person_id, 
3.      ps.person_name, 
4.      ( 
5.          select txt from 
6.          ( 
7.              select 
8.                  pa.person_text as txt, 
9.                  pa.person_reference as personid, 
10.                  row_number() over (order by pa.last_modified_date desc) r 
11.              from 
12.                  Person_Academia pa 
13.              where 
14.                  pa.type = 'USER' 
15.                  AND pa.code = 'SPECIAL1' 
16.          ) 
17.              where personid = ps.person_id 
18.   
19.      ) as AcademicInfo 
20.  FROM 
21.      Person_Staff ps