Re: Subquery to return top row

Given that your sub-query includes

    order by goremal_preferred_ind DESC
and the predicate

    AND goremal_preferred_ind = 'Y'
this part of the ordering seems redundant, so the following may work for 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 goremal_activity_date =
> (
> SELECT max(goremal_activity_date)
> FROM goremal
> WHERE goremal_pidm = spriden_pidm
> AND goremal_emal_code = 'EML'
> AND goremal_status_ind = 'A'
> AND goremal_preferred_ind = 'Y'
> )
>;

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

ahporter_at_montana.edu wrote in message <8j8g47$urg$1_at_nnrp1.deja.com>...


>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.