sqlite - nested subquery issue -

shown below company table extract latest unique dept row records particular id. algorithm followed achieve requirement mentioned below. problem facing not able figure out 3rd step in algorithm embedded nested query. please suggest.

    id             state    dept        datetime          yr_mon_date     ================================================================          a8178         state2    account  2016:09:01 14:11:44  2016:09:01      a8178         state3    account  2016:09:01 15:12:50  2016:09:01      a8178         state3    account  2016:09:01 16:11:33  2016:09:01      a8178         state3    sales    2016:09:01 18:19:34  2016:09:01      a8178         state2    sales    2016:09:01 18:28:50  2016:09:01      a8178         state3    sales    2016:09:01 18:35:22  2016:09:01  

algorithm

1. list ids has dept = 'sales'
2. for particular dept(sales) , select ids have max(datetime);
3. from obtained ids, select row has max(datetime) , dept <> sales;

step:1 + step:2

    select id, state, dept, datetime, substr(datetime, 1, 10) yr_mon_date company t1      (state = 'state1' or state = 'state2' or state = 'state3' ) ,      yr_mon_date = '2016:09:01' , dept = 'sales' , datetime =     (select max(t2.datetime) company t2  t2.id = t1.id  ); 

expected output

    id             state    dept        datetime          yr_mon_date     ================================================================      a8178         state3    account  2016:09:01 16:11:33  2016:09:01      a8178         state3    sales    2016:09:01 18:35:22  2016:09:01  

your algorithm wrong. want retrieve recent records per id per dept. can retrieve recent transactions using max function in subquery. , per id per dept joining on these columns in subquery.

select id, state, dept, datetime company t1 datetime = ( select max(t2.datetime) company t2 t2.id = t1.id , t2.dept = t1.dept )