I can't get INNER JOIN to work "ORA 00918"

Question

I have a query that returns all the correct rows I need which looks like this:

SELECT sequence_no,
MAX(time)
FROM ensapp.shop_order_analysis
WHERE analyzer='SORVI01'
AND part_no in ('PTS228AL','PSS1285')
AND sequence_no in ('6','9','*')
AND SYSDATE-15<time group by sequence_no

But I want it to return one more column and when I try to do an INNER JOIN it says "ORA-00918: Column ambiguously defined". I tried to google this but couldn't make any sense out of it. This is what I tried:

SELECT part_no, sequence_no, time
FROM ensapp.shop_order_analysis a
INNER JOIN(
SELECT sequence_no,
MAX(time)
FROM ensapp.shop_order_analysis
WHERE analyzer='SORVI01'
AND part_no in ('PTS228AL','PSS1285')
AND sequence_no in ('6','9','*')
AND SYSDATE-15<time group by sequence_no) c 
ON a.sequence_no=c.sequence_no

I am a beginner in SQL. Thanks in advance

Answer 1

First, always qualify your column names. Then you will never have this problem:

SELECT a.part_no, a.sequence_no, a.time
FROM ensapp.shop_order_analysis a INNER JOIN
     (SELECT sequence_no, MAX(time) as maxt
      FROM ensapp.shop_order_analysis
      WHERE analyzer = 'SORVI01' AND
            part_no in ('PTS228AL','PSS1285') AND
            sequence_no in ('6', '9', '*') AND
            SYSDATE-15 < time
      GROUP BY sequence_no
     ) aa
     ON a.sequence_no = aa.sequence_no;

I am guessing, though, that you really want:

SELECT a.*, MAX(a.time) OVER () as maxt
FROM ensapp.shop_order_analysis s
WHERE analyzer = 'SORVI01' AND
      part_no in ('PTS228AL','PSS1285') AND
      sequence_no in ('6', '9', '*') AND
      SYSDATE - 15 < time;