CODEWITHSUNDEEP
sqloracle11gsubquery
Jonnny
Jonnny

Reputation: 5039

Select highest value of subquery in oracle

I have the following query:

SELECT P.ID,
(
SELECT F.VALUE_ID FROM FORM F
INNER JOIN F.PERSON_ID = P.ID
) AS LATEST_FORM
FROM FORM F1 INNER JOIN PERSON P
ON P.ID = F1.PERSON_ID
WHERE F1.NO_PCP_IND IS NOT NULL
ORDER BY P.ID

Each person can have multiple forms and I'm just trying to get the latest form they have submitted. I tried using ROWNUM inside the subquery and I understand why it doesn't work but not sure how to make it generate the right result set with Oracle.

Upvotes: 0

Views: 1539

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

This is the query:

SELECT P.ID,
       (SELECT F.VALUE_ID
        FROM FORM F INNER JOIN
             F.PERSON_ID = P.ID
       ) AS LATEST_FORM
FROM FORM F1 INNER JOIN
     PERSON P
     ON P.ID = F1.PERSON_ID
WHERE F1.NO_PCP_IND IS NOT NULL
ORDER BY P.ID;

In addition to being syntactically incorrect, this would return one row per person and user if it worked. I think your original query should be more like:

SELECT P.ID,
       (SELECT F.VALUE_ID
        FROM FORM F
        WHERE F1.NO_PCP_IND IS NOT NULL AND F.PERSON_ID = P.ID AND
              ROWNUM = 1
       ) AS LATEST_FORM
FROM PERSON P
ORDER BY P.ID;

This doesn't fix your problem, because you want to keep the last value. For that, use the keep functionality:

SELECT P.ID,
       (SELECT MAX(F.VALUE_ID) KEEP (DENSE_RANK FIRST ORDER BY XXX DESC)
        FROM FORM F
        WHERE F1.NO_PCP_IND IS NOT NULL AND F.PERSON_ID = P.ID
       ) AS LATEST_FORM
FROM PERSON P
ORDER BY P.ID;

In this case XXX is the name of the column that defines the ordering of the forms for each person.

Upvotes: 2

deejay
deejay

Reputation: 575

I believe its the ValueId which tells about the latest form

So wouldn't this work??

SELECT max(F.VALUEID), P.ID
FROM FORM F, PERSON P
where F.PID = P.ID

Upvotes: 0

Related Questions