What?
A quick note on how I got round one this one.

Why?
Often enough, our requirement is that the latest record from another table is associated with the current row, and often enough we get the latest by ordering the dataset of the subquery. In T-SQL and MySQL, this is not so much of an issue.

I get this error when having to use an ORDER BY clause in a subquery within an Oracle 11g environment.

How?
Consider the following:

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.
So you can see my parentheses are fine.

Working Solution:
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

Add comment


Send

Comments   

0 Art Friday, 20th January 2017, 3:13 pm
Hello,
what if I would like to add rownum to that code, but in my case it's all is subquery? How can I do this ?