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]
Drew's query should do exactly that.
You also asked how his query works. Here is a short explanation:
First, in "WITH Combined AS (...)", the data from the two tables is simply concatenated. Nothing special.
Then, in ", Ordered AS (...)", that concatenated data is extended with one extra column, named rn, and based on the ROW_NUMBER() function. The entire set of data returned from the first part is partitioned (logically divided in groups) based on MachineID and InstanceKey, so rows with the same MachineID and InstanceKey are in the same group, and rows with a difference in one or both column are in another group. Within each group, the rows are then numbered by descending TimeKey, so the most recent gets number 1, the next gets number 2, etc.
Finally, in "SELECT ... WHERE rn = 1", the results from that second part are filtered and presented. The filter keeps only the rows with rn = 1, which based on the above means that you will get only the most recent row for any distinct MachineID/InstanceKey combination.
To get more insight in this, you can (temporarily) replace the last part with simple selects such as SELECT * FROM Combined or SELECT * FROM Ordered - that way you can see exactly what data is returned from those two parts of the query.