Remote Database is DB2.
I have table a which is a list of part numbers.
Table b is a list of inspections performed on these part numbers. This table can have multiple records of each part number. Each record has a datetime stamp field and a status.
I need a list of part numbers from table a where the last record(greatest datetime stamp) is status 2(rejected).
Select partno From table a Where partno in (Select partno From table b Where status = 2 Order By datetime stamp Desc Fetch First 1 Rows Only)
This gives me the first record of the part number group with a status of 2. However, there is a newer record with a status of 1 so this record should not be in result set.