Oracle 10g Inner Join with Limit clause?

Question

I feel like this should be easy, but i am struggling to return the top result from an inner join select. This is the query:

SELECT * FROM (
    SELECT *
    FROM PROCESS_OWNER.ARTIFACTS, PROCESS_OWNER.ARTIFACT_METADATA
    WHERE ARTIFACTS.ARTIFACT_ID = ARTIFACT_METADATA.ARTIFACT_ID
    AND ARTIFACTS.ARTIFACT_LABEL = 'getDBStatus'
    ORDER BY ARTIFACTS.REGISTERED_TIMESTAMP DESC 
)
WHERE ROWNUM = 1

Database is Oracle 10g. The error i get is: 00918. 00000 - "column ambiguously defined"

The inner query works fine - returing some 38 records ordered by TIMESTAMP, i just want the most recent (top one)

Thanks for any help

Answer 1

Your inner query returns two columns called ARTIFACT_ID - one from each table. When you nest that in another select, it results in the error you see. You need to unambiguously list the columns you want in the inner select.

Oddly, it appears that if you re-write it with an ANSI join, it works:

SELECT * FROM (
    SELECT *
    FROM PROCESS_OWNER.ARTIFACTS
    JOIN PROCESS_OWNER.ARTIFACT_METADATA
    ON ARTIFACTS.ARTIFACT_ID = ARTIFACT_METADATA.ARTIFACT_ID
    WHERE ARTIFACTS.ARTIFACT_LABEL = 'getDBStatus'
    ORDER BY ARTIFACTS.REGISTERED_TIMESTAMP DESC 
)
WHERE ROWNUM = 1