Forum Discussion
Merge queries causing duplicate records
I am merging two queries together and it is creating duplicate records in the output. I have verified that there are no duplicates in either of the base queries as shown below
Table "States"
| Valve | timestamp | string_value | name | Index | TS-MS | Dt |
| XV017045 | 2025-08-04T00:03:26.0110015-05:00 | UNDEFINED | PV_D | 19400 | 8/4/2025 12:03:26.011 | 8/4/2025 |
| XV017045 | 2025-08-04T00:03:27.0070037-05:00 | CLOSED | PV_D | 19401 | 8/4/2025 12:03:27.007 | 8/4/2025 |
| XV017045 | 2025-08-04T08:03:27.0070037-05:00 | CLOSED | PV_D | 19403 | 8/4/2025 08:03:27.007 | 8/4/2025 |
| XV017045 | 2025-08-04T11:23:27.0070037-05:00 | CLOSED | PV_D | 19405 | 8/4/2025 11:23:27.007 | 8/4/2025 |
| XV017045 | 2025-08-04T11:33:27.0070037-05:00 | CLOSED | PV_D | 19406 | 8/4/2025 11:33:27.007 | 8/4/2025 |
| XV017045 | 2025-08-04T12:03:27.0070037-05:00 | CLOSED | PV_D | 19409 | 8/4/2025 12:03:27.007 | 8/4/2025 |
| XV017045 | 2025-08-04T12:10:03.5070037-05:00 | OPEN | PV_D | 19411 | 8/4/2025 12:10:03.507 | 8/4/2025 |
| XV017045 | 2025-08-04T12:50:58.5070037-05:00 | CLOSED | PV_D | 19413 | 8/4/2025 12:50:58.507 | 8/4/2025 |
| XV017045 | 2025-08-04T20:50:58.5070037-05:00 | CLOSED | PV_D | 19415 | 8/4/2025 08:50:58.507 | 8/4/2025 |
Table "RS Samples"
| timestamp | numeric_value | string_value | warning | ValveNm | TS-MS |
| 2025-08-04T00:03:26.0110015-05:00 | null | 0 | null | XV017045 | 8/4/2025 12:03:26.011 |
| 2025-08-04T00:03:27.0070037-05:00 | null | 0 | null | XV017045 | 8/4/2025 12:03:27.007 |
| 2025-08-04T08:03:27.0070037-05:00 | null | 0 | null | XV017045 | 8/4/2025 08:03:27.007 |
| 2025-08-04T11:23:27.0070037-05:00 | null | 0 | null | XV017045 | 8/4/2025 11:23:27.007 |
| 2025-08-04T11:33:27.0070037-05:00 | null | 0 | null | XV017045 | 8/4/2025 11:33:27.007 |
| 2025-08-04T12:03:27.0070037-05:00 | null | 0 | null | XV017045 | 8/4/2025 12:03:27.007 |
| 2025-08-04T12:10:03.5070037-05:00 | null | 1 | null | XV017045 | 8/4/2025 12:10:03.507 |
| 2025-08-04T12:50:58.5070037-05:00 | null | 0 | null | XV017045 | 8/4/2025 12:50:58.507 |
| 2025-08-04T20:50:58.5070037-05:00 | null | 0 | null | XV017045 | 8/4/2025 08:50:58.507 |
Join (left outer) from States to RS Samples via Valve -> ValveNm and TS-MS -> TS-MS (TS-MS is a text datatype)
Result
| Valve | timestamp | string_value | name | Index | TS-MS | Dt | RS Samples.string_value |
| XV017045 | 2025-08-04T00:03:26.0110015-05:00 | UNDEFINED | PV_D | 19400 | 8/4/2025 12:03:26.011 | 8/4/2025 | 0 |
| XV017045 | 2025-08-04T00:03:27.0070037-05:00 | CLOSED | PV_D | 19401 | 8/4/2025 12:03:27.007 | 8/4/2025 | 0 |
| XV017045 | 2025-08-04T00:03:27.0070037-05:00 | CLOSED | PV_D | 19401 | 8/4/2025 12:03:27.007 | 8/4/2025 | 0 |
| XV017045 | 2025-08-04T08:03:27.0070037-05:00 | CLOSED | PV_D | 19403 | 8/4/2025 08:03:27.007 | 8/4/2025 | 0 |
| XV017045 | 2025-08-04T11:23:27.0070037-05:00 | CLOSED | PV_D | 19405 | 8/4/2025 11:23:27.007 | 8/4/2025 | 0 |
| XV017045 | 2025-08-04T11:33:27.0070037-05:00 | CLOSED | PV_D | 19406 | 8/4/2025 11:33:27.007 | 8/4/2025 | 0 |
| XV017045 | 2025-08-04T12:03:27.0070037-05:00 | CLOSED | PV_D | 19409 | 8/4/2025 12:03:27.007 | 8/4/2025 | 0 |
| XV017045 | 2025-08-04T12:03:27.0070037-05:00 | CLOSED | PV_D | 19409 | 8/4/2025 12:03:27.007 | 8/4/2025 | 0 |
| XV017045 | 2025-08-04T12:10:03.5070037-05:00 | OPEN | PV_D | 19411 | 8/4/2025 12:10:03.507 | 8/4/2025 | 1 |
| XV017045 | 2025-08-04T12:50:58.5070037-05:00 | CLOSED | PV_D | 19413 | 8/4/2025 12:50:58.507 | 8/4/2025 | 0 |
| XV017045 | 2025-08-04T20:50:58.5070037-05:00 | CLOSED | PV_D | 19415 | 8/4/2025 08:50:58.507 | 8/4/2025 | 0 |
The ones in red are exact duplicates of each other.
What could be causing this, and is there a way to mitigate without using "Remove Duplicates" (the actual data set is nearly 1,000,000 rows each) ? The two queries come from different base tables, so creating an Index on RS_Samples for matching would not work.
I am on version 2.154.956.0 64-bit (May 2026)
Thanks,
David
Can you verify that there are no duplicates when using the TS-MS column. From the example data you provided it looks like midnight and 12pm are both showing as 12:00:00 in your text column. This could be what is causing the duplicates...
2 Replies
Can you verify that there are no duplicates when using the TS-MS column. From the example data you provided it looks like midnight and 12pm are both showing as 12:00:00 in your text column. This could be what is causing the duplicates...
jgeddes - thank you so very much. I have been staring at this for days. Fixed the creation of the TS-MS column to use HH instead of hh in the format and the duplicates are gone.
