I have a sql query I use in SSMS that pulls data out of a single table and it has Max and Group by functions in it that I can't seem to get right in FME, if even possible. I cannot seen to figure out the syntax, if different from SQL in FME.

You're doing an order by "MAX (YR_TXT)", but there is no column with that name because you have used the alias LAST_YR_DVL.

I remember there can be some weirdness with using an order by (due to how the statement is executed), but usually it just puts a warning in the log.

The main syntax difference to be aware of is needing to specify FME_SQL_DELIMITER ; if you have multiple statements to execute in one script.

The @Value(attribute) is commonly used in a SQLExecutor. The order things work is that FME will first substitute in the @Value attribute values into the script, and then execute it on the database. You would do that if you wanted to execute a different statement for each feature passed into the SQLExecutor, often in a Where clause, or an insert statement.

A statement like your example would go into a SQLCreator usually because it has no inputs from the rest of the FME workspace.

If the order by is the cause of the issue, then one option is to remove it, and add a Sorter after the SQLCreator, although this will be less efficient than having the SQL database do the sorting.

I think from memory FME executes a select SQL statement something like:

SELECT * FROM ( your sql here ) WHERE 1=1

and this breaks the order of things, because there shouldn't be an Order By in a subquery. But I'm sure I've seen it work, just gives a warning in the log.