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

There must be a column that is common (in name) to both ATRIFACTS and ARTIFACT_METADATA.

This is OK in the inner query itself but when the outer query comes to assign names to the columns there is a conflict.

Try naming the inner query columns specifically (naming the similarly named columns something distinct) and it should work OK.

Better still, only select the columns you absolutely need in the inner query rather than SELECT *

Answer 2

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