0

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?

  • Have you used "group by" clause in your query. – Md Haidar Ali Khan Mar 12 '15 at 7:39
  • How is the "latest" defined? By what column? – ypercubeᵀᴹ Mar 12 '15 at 9:49
  • tableB, tableC and tableD are tests done and the date_test refers to the date that the testing is done. so latest is defined by the latest for the dv.date_test, dw.date_test and tt.date_test – breaking_brain Mar 13 '15 at 2:14
5

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 ;
  • so each part undergoes 3 different tests(dv,dw,tt) and the date_test record the date of the test being done. but if the part fails any of the test, it would need to undergo a re-test. when i query it using my query, i would have duplicates of the part_id as it has undergone a re-test. – breaking_brain Mar 13 '15 at 2:24
  • dv.results, dw.results, tt.results identifies the testing. dv.date_test, dw.date_test, tt.date_test are the columns used for ordering. so i think the intent is to get distinct data from dw.results by selecting only data that is latest in dw.date_test and join it with dv.results selecting latest by dv_date.test and join it with tt.results selecting latest by tt.date_test. – breaking_brain Mar 13 '15 at 2:32
  • would you mind briefly explaining what the row_number() section does? the query seems to work alright! – breaking_brain Mar 13 '15 at 5:56
-3

Use JOIN with ON condition. Try as like below

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 
     INNER JOIN tableB dv ON lb.part_id = dv.part_id 
     INNER JOIN tableC dw ON lb.part_id = dw.part_id 
     INNER JOIN tableD tt ON lb.part_id = tt.part_id
Where dv.date_test = dw.date_test 
  AND dw.date_test = tt.date_test
  AND dv.date_test = NOW()

All the best.

  • thanks for the reply! but it just says NOW is an invalid identifier.. – breaking_brain Mar 12 '15 at 8:17
  • i think its an oracle database and it does not recognize the NOW() function. i tried using sysdate but there are no results returned at all – breaking_brain Mar 12 '15 at 8:26
  • Which database you are using ? . I just used now() for example you can use any timestamp. – KESAVAN PURUSOTHAMAN Mar 12 '15 at 8:30
  • i am using oracle database. but i do not understand how does the timestamp help to return the latest results. there are 3 different tables where the data of 3 different tests are being sent to. in each test there may be re-tests and i would only like the latest results from the tests. – breaking_brain Mar 12 '15 at 8:33
  • Use equivalent Oracle function for current timestamp as CURRENT_TIMESTAMP() or CURTIME() whatever you require. For more see here docs.oracle.com/cd/E17952_01/refman-5.1-en/… – KESAVAN PURUSOTHAMAN Mar 12 '15 at 8:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.