tsql - SQL get max date and join on other table -

sorry duplicate i've been through other answers has helped me sql below can't figure out how manage join subquery gets max date.

select mo_number, -- systems_test_status, else combiner_test_data cdt.test_index, cdt.datetime, lumina_current_1, power_reading_1, lumina_current_2, power_reading_2, lumina_current_3, power_reading_3, lumina_current_4, power_reading_4 most_recent, step_pass_status  combiner_test_data cdt  inner join systems_test_status on cdt.test_index = systems_test_status.test_index  --join(select --  test_index, --  max(datetime) most_recent_time --  combiner_test_data subcdt --  group test_index) joincdt on cdt.test_index = joincdt.test_index --  , cdt.datetime = joincdt.most_recent_time  lumina_current_2 > 12 

the join , subquery work fine separately output few rows whereas i'd expect few thousand. i've commented out subquery in example above. reason need inner join return systems_test_status.mo_number joining on test_index.

running without subquery correctly returns 48,000 records stress testing electrical assets. many of these records pertain same asset (the reference test_index). each asset gets tested many times.

running subquery alone correctly returns recent test date each asset.

i'm trying recent test each asset.

thanks

you can use row_number function set max date row 1 , select record. solution assumes mo_number uniquely identifies each asset. if not, change partition by in row_number function column uniquely identifies asset.

select * ( select mo_number,  combiner_test_data cdt.test_index, cdt.datetime, lumina_current_1, power_reading_1, lumina_current_2, power_reading_2, lumina_current_3, power_reading_3, lumina_current_4, power_reading_4 most_recent, step_pass_status, row_number() over(partition mo_number order datetime desc) rn combiner_test_data cdt inner join systems_test_status on cdt.test_index = systems_test_status.test_index lumina_current_2 > 12 ) t rn = 1;