"order by transdate desc" will just reverse the order of values in the set but it is not going to filter and return only 1 latest value. Use max(), row_number(), etc. functions depending upon the database that you are using with Maximo to write a query that gives you the latest record.
Eg- If I need to find the latest status from Work Order History.
wonum = :wonum and siteid = :siteid
and wostatusid = (
where wonum = :wonum and siteid = :siteid
Even if you write an automation script, you need to put a logic of where clause in it and it eventually fires a SQL clause on the Database which you can do using relationship.