Joining 4 tables and selecting distinct values based on 3 timestamps

optimizationoracle

I have been trying to remove duplicates from my query results based on the time but I would need to consider 3 different times and i am not sure how to do that. As some of the testing happen multiple times, there are duplicates on the part_id and i would only like to have the last tested results from all the 3 tables.

I am running this query now

select lb.request_ky,
lb.build_dm,
lb.part_id,
dv.date_test,
dv.result,
dw.date_test,
dw.result,
tt.date_test,
tt.result

From tableA lb,
tableB dv,
tableC dw,
tableD tt

Where lb.part_id = dv.part_id
And lb.part_id = dw.part_id
And lb.part_id = tt.part_id

This query would give me duplicates on the part id. How can i just get the latest result from each table and join them together?

Best Answer

You have to define first how a "testing" is identified and what "latest" is. Which of the columns identifies a testing and which of the date columns is going to be used for the ordering?

Then, one way to write the query is using window functions:

With cte As
  ( Select 
        lb.request_ky,
        lb.build_dm,
        lb.part_id,
        dv.date_test As date_test1,
        dv.result    As result1,
        dw.date_test As date_test2,
        dw.result    As result2,
        tt.date_test As date_test3,
        tt.result    As result3,
        Row_number() Over (Partition By          -- pick a column for the
                             lb.part_id          -- identification
                           Order By              -- and another for the 
                             dv.date_test Desc)  -- order
            As rn
    From 
        tableA lb
      Join tableB dv
        On lb.part_id = dv.part_id
      Join tableC dw
        On lb.part_id = dw.part_id
      Join tableD tt
        On lb.part_id = tt.part_id
  )
Select 
    request_ky,
    build_dm,
    part_id,
    date_test1,
    result1,
    date_test2,
    result2,
    date_test3,
    result3
From
    cte
Where
    rn = 1 ;
Related Question