February 10, 2016 at 6:10 am
kavithaje (2/10/2016)
Hi Allen,The query is returning duplicate rows. i have same MachineID and InstanceKey in both tables. only Account. Schema class and time key is different. I mean Work_Station_Admin_1 got updated with new values.
Now it should retrieve only 1 row with latest timekey(Work_Station_Admin_1). But am getting both rows.
How can i get only 1 row with the latest TimeKey?
Work_Station_Admin_1
MachineIDInstanceKeyTimeKeyRevisionIDAccountSchemaClassrowversion
[1234567][1][2/10/2016 10:00][1][2][xyz][0x000000000084B0CB]
Work_Station_Admin_2
MachineIDInstanceKeyTimeKeyRevisionIDAccountSchemaClassrowversion
[1234567][1][11/11/2015 15:14][1][1][abc][0x000000000084B0CC]
What is 'Work_Station_Admin_1' and how does it relate to your data?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 10, 2016 at 8:28 am
It is just dummy table name. Query is working fine now.
Am able to get single record based on time stamp.
Thank you.
February 10, 2016 at 8:58 am
kavithaje (2/10/2016)
It is just dummy table name. Query is working fine now.Am able to get single record based on time stamp.
Thank you.
Perhaps you would describe what was wrong with Drew's query and what you did to fix it?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 11, 2016 at 12:37 pm
Old school query without CTE and windowing functions:
SELECT MachineID, InstanceKey, TimeKey, RevisionID, rowversion, Account, SchemaClass FROM TABLE_A
where not exists(
SELECT TABLE_B.MachineID FROM TABLE_B
where TABLE_B.MachineID = TABLE_A.MachineID
and TABLE_B.InstanceKey = TABLE_A.InstanceKey
and TABLE_B.TimeKey > TABLE_A.TimeKey
) -- select from table a when there is not a matching column in Table b with a larger TimeKey
UNION ALL
SELECT MachineID, InstanceKey, TimeKey, RevisionID, rowversion, Account, SchemaClass FROM TABLE_B
where not exists(
SELECT TABLE_A.MachineID FROM TABLE_A
where TABLE_A.MachineID = TABLE_B.MachineID
and TABLE_A.InstanceKey = TABLE_B.InstanceKey
and TABLE_A.TimeKey >= TABLE_B.TimeKey
) -- select from table b when there is not a matching column in Table a with a larger TimeKey
February 12, 2016 at 1:53 am
Drew's query is perfectly fine. The issue was with my data. when i checked the data from source table, Machine ID and Instance Key were not primary key. hence few rows were having null values. when we do the combination of Machine ID and Instance Key, it was retrieving 2 rows because either 1 column was having null value.
Since source DB is read only, i cannot edit the Source DB. so is there any solution to ignore null value?
February 12, 2016 at 3:05 am
Helpseeker (2/12/2016)
Drew's query is perfectly fine. The issue was with my data. when i checked the data from source table, Machine ID and Instance Key were not primary key. hence few rows were having null values. when we do the combination of Machine ID and Instance Key, it was retrieving 2 rows because either 1 column was having null value.Since source DB is read only, i cannot edit the Source DB. so is there any solution to ignore null value?
Perhaps use WHERE column IS NOT NULL ?
If that's not what you mean, post CREATE TABLE and INSERT statements with sample data, expected output, and a good explanation.
February 12, 2016 at 9:11 am
In the combined cte just add a where clause to both tables to filter out the null records.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply