Subquery to return top row

I need to have a subquery return a particular row; I can create a normal query to get several rows and I can get the row I need to come to the top with ORDER BY. However, when I try to embed this query as a subquery, I get a "missing right parenthesis" error on the ORDER keyword.

First, is it possible to use ORDER BY in a subquery?

Second, I need to limit the results of the subquery to just the first row -- it is possible to somehow just select the top row? In other words, the row with rownum = 1?

Here is the skeleton SQL that I currently have. What I need is the goremal_email_address that floats to the top in the subquery. The ????? marks are things that I do not know how to fill in. And I have no idea how to select just the first row that is returned. I have seen other examples that indicate this can be done; I just cannot figure out the syntax to accomplish it.

Help! Thank you!

SELECT

	  SUBSTR(spriden_first_name,1,12) "FIRST"


	  ,SUBSTR(spriden_last_name,1,12) "LAST"

	  ,goremal_email_address "E-MAIL"    <-- need one of these

FROM
	  spriden,goremal
WHERE
          goremal_pidm = spriden_pidm
AND       ????? =
	  	(
		SELECT    ?????
		FROM	  goremal
		WHERE	  goremal_pidm = spriden_pidm
		AND       goremal_emal_code = 'EML'
		AND	  goremal_status_ind = 'A'
		AND	  goremal_preferred_ind = 'Y'
		ORDER BY  goremal_preferred_ind DESC,
                          goremal_activity_date DESC
		)

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Jun 26 2000 - 00:00:00 CDT