select max(ts) as maxtime,t.name ,t.namecode from table1 t
where exists(Select * from cr_table1 where name = t.name and namecode = t.namecode )
group by ts,t.name ,t.namecode ;
select * from T T1
WHERE NOT EXISTS(SELECT 1 FROM T WHERE name=T1.name and namecode=T1.namecode AND thetime>T1.thetime)
select name,namecode,max(thetime) thetime
from T
GROUP BY name,namecode
select name,namecode,thetime
from(select T.*,row_number()over(partition by name,namecode order by thetime desc) RN)
WHERE RN=1
