It seems that ordering within an IN clause does not work.
where user_id in (select user_id from table2 order by created_date desc) #desc so that I get the newest entries only
Running this query will always give me the same results. It seems that the user_id is not being sorted correctly.
I have been googling and it seems that the field() function may work for me.
But based on the documentation and examples I've seen so far, it seems that I have to run the select sub query twice? I'm not sure if I'm understanding it correctly.
where user_id in (select user_id from table2)
order by field(user_id, select user_id from table2) #I need desc here
But I'm afraid this would also be slow (I'm moving the query from a join to IN instead) because the indexed column would be inside the function. :-/
I may result in using a stored procedure in order to sort the query and then pass the result set within the IN clause.
If anyone can shed some light please and thanks.