0

I have a table (e_v2) with some time-series data. I want to fetch all rows where the data in the "POINT_KEY" column appears in the "KEY" column in another table (etraining_v1). The code below (I'm unsure whether it's correct) did return some rows, but there are many duplicates. I would like to exclude the duplicates.

SELECT * FROM e_v2
WHERE EXISTS (SELECT FROM etraining_v1 WHERE "POINT_KEY" = etraining_v1.key)
ORDER BY 
"POINT_KEY",
"INTERVAL_READING"

1 Answer 1

0

Each row of e_v2 will be returned at most once by your query. Any duplicate values you are getting are duplicates that already exist in e_v2. You can remove them by adding a "DISTINCT" before your select-list.

SELECT DISTINCT * FROM e_v2
WHERE EXISTS (SELECT 1 FROM etraining_v1 WHERE "POINT_KEY" = etraining_v1.key)
ORDER BY 
"POINT_KEY",
"INTERVAL_READING"

Or you an clean up e_v2 so that it doesn't contain duplicates.

2
  • Does the 1 after SELECT that you added instructs the query to only select 1 row where "POINT_KEY" matches etraining_v1.key? If so, is the distinct still necessary ? Thanks
    – abigfatcat
    Aug 29, 2019 at 2:26
  • No, it makes no difference to that. I added it just because I think it is clearer and more portable to write it that way. An empty select-list is a peculiarity.
    – jjanes
    Aug 29, 2019 at 7:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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