SQL command for conditional join for PROGRESS database

Question

Please bear with me new to SQL- I am trying to write an SQL command with a join in a PROGRESS db. I would like to then select only the first matching record from the join. I thought to use LIMIT but PROGRESS does not support that. MIN or TOP would also work I think but having trouble with the syntax. Something like this?-

SELECT table1.field 1, table2.field 2
FROM table2
INNER JOIN table2
ON table1.field3=table2.field3
WHERE table1.field4 in (SELECT min(table1.field4) FROM table1)

BUt it appears I can't use MIN there as saying can't do an aggregate there. Any help would be huge.

Answer 1

try:

SELECT
    t1.field1, t2.field2
    FROM table1            t1
        INNER JOIN table2  t2 ON t1.field3=t2.field3
    WHERE t1.field4=(SELECT min(t.field4) FROM table1 t WHERE t1.field4=t.field4)