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"
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.

| improve this answer | |
  • 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 '19 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 '19 at 7:35

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.