I wrote a query to compare 2 columns in different tables (
TRELAY.ID = TUSERDEF8.N01
TUSERDEF8.S04, TUSERDEF8.N01, TUSERDEF8.S06
TUSERDEF8 ON TRELAY.ID = TUSERDEF8.N01
LEFT(TRELAY.S15, 1) <> LEFT(TUSERDEF8.S04, 1)
AND NOT (TRELAY.S15 LIKE '%MEDIUM%' AND
TUSERDEF8.S04 LIKE '%N/A%' AND
TUSERDEF8.S06 LIKE '%EACMS%')
Using an ID column to determine which row is "last" is a bad idea
Using cryptic table names like "TUSERDEF8" (how is it different from TUSERDEF7) is a very bad idea, along with completely cryptic column names like "S04".
Using prefixes like "T" for table is a bad idea - it should already be clear that it's a table.
Now that all of that is out of the way:
SELECT R.ID, R.S15, U.S04, U.N01, U.S06 FROM TRELAY R INNER JOIN TUSERDEF8 U ON U.N01 = R.ID LEFT OUTER JOIN TUSERDEF8 U2 ON U2.N01 = R.ID AND U2.ID > U.ID WHERE U2.ID IS NULL AND -- This will only happen if the LEFT OUTER JOIN above found no match, meaning that the row in U has the highest ID value of all matches LEFT(R.S15, 1) <> LEFT(U.S04, 1) AND NOT ( R.S15 LIKE '%MEDIUM%' AND U.S04 LIKE '%N/A%' AND U.S06 LIKE '%EACMS%' )