Re: Subquery to return top row

From: Dmitry Pugachev <dev_at_ellink.ru>
Date: 2000/06/28
Message-ID: <8jd5hk$bjv$1@news.sovam.com>#1/1

Hello, again

May be I was not clear enough, I had mentioned about SECOND subquery like this one:

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 goremalOUT
WHERE

          goremal_pidm = spriden_pidm
AND       goremal_preferred_ind =

(
SELECT MAX(goremal_preferred_ind)
FROM goremal
WHERE goremal_pidm = spriden_pidm
AND goremal_emal_code = 'EML'
AND goremal_status_ind = 'A'
)
AND goremal_activity_date =
(
SELECT MAX(goremal_activity_date)
FROM goremal goremalIN
WHERE goremal_pidm = spriden_pidm
AND goremal_emal_code = 'EML'
AND goremal_status_ind = 'A'
AND goremalIN.goremal_preferred_ind = goremalOUT.goremal_preferred_ind );

Hth,
Dmitry Pugachev, Oracle DBA

• Original Message ----- From: <ahporter_at_montana.edu> Newsgroups: comp.databases.oracle.server Sent: Wednesday, June 28, 2000 1:11 AM Subject: Re: Subquery to return top row

> Thank you, Dmitry.
>
> Unfortunately in this case I do need the ORDER BY to get the correct row
> to the top. It is dependent first on the PREFERRED_IND and only then on
> the ACTIVITY_DATE. Just using MAX(ACTIVITY_DATE) will return the newest
> entry but not necessarily the preferred entry. If there is a preferred
> entry I need that one; if not, then I need the newest one.
>
> It is a good trick for a PL/SQL function that I can reference from the
> SQL SELECT, but I cannot store a function in the database.
>
> Thank you for your thoughts. I am no closer to a solution but it has
> helped clarify my thinking. Looks like ORDER BY is good in Oracle 8. I
> saw it in some other posts in this newsgroup.
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Wed Jun 28 2000 - 00:00:00 CDT