I have 2 tables. Table A has information about inactivated print jobs and Table B has the description for Inactivation.

Note: Print jobs can be reactivated and again Inactivated. That is why Table A can have the same print job id more than once.

Now my objective is to retrieve Inactivated print job names and the reason for inactivating. Also if there is a print job that was inactivated twice, it should retrieve the most recent date of inactivation.

The following is my query:

  1  SELECT PRJOB_ID, MAX(INACTIVE_DATE)AS INADAT, DESCR
  2  FROM Table A, Table B
  3  WHERE Table A.REASONCODE_ID=RASCODE
  4* GROUP BY PRJOB_ID, DESCR

Output:

PRJOB_ID   INADAT        DESCR
---------- ---------    ----------
AA1        03-FEB-11    NEWD DESC
AA2        05-FEB-11    ABCD
AA2        07-FEB-11    NEWD DESC
AA3        09-FEB-11    NEWD DESC

PROBLEM IS:

Along with 'AA1'and 'AA3', I ONLY need the most recently inactivated print job for 'AA2'.




Table A (TABID is pk for this table)

     TABID INACTIVE_ PRJOB_ID   REASONCODE
---------- --------- ---------- ----------
         1 03-FEB-11 AA1        RC1
         2 05-FEB-11 AA2        RC2
         3 07-FEB-11 AA2        RC1
         4 09-FEB-11 AA3        RC1



Table B

RASCODE    DESCR
---------- ----------
RC1        NEWD DESC
RC2        ABCD