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.

Not being an SQL guru although having some SQL training. The nearest I can tell was when I tried to order on the alias assigned, SSMS didn't like that so using the original field name made it work (I built this query probably a dozen years ago for its intended purpose). This query fails outright so I am guessing it is because FME likes things like @value(field name)? instead of the field name but even replacing the current query w/ those values I still had no luck.

I can run the query in the reader again to get the actual error if needed if that helps. I am just running off memory right now.

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.