>I have a special request to get a list of records, where there is a bunch of INNER JOIN, but in one specific INNER JOIN, I need to get the most recent record. As far as I know, there is no way to obtain that directly from a INNER JOIN syntax. My assumption is that I would have to go sub query for this one. Any comment?

Yes, you would, unless you joined a view or a table function.