Select Latest record by comparing 2 tables.

  • 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?

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • It is just dummy table name. Query is working fine now.

    Am able to get single record based on time stamp.

    Thank you.

  • 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?

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • 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

  • 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?

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


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • 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