Limit rows in Correlated Subquery

I have one big and two small issues with my query I need help with. First, I want a SQL (postgres if it matters) query that gets SUM of last two ratings of fruits and makes a new column for each fruit column on original table with corresponding result. Conditions are that it has to have happened in the past (before current row date) and I only want the last 2 records and column for 'rush_order' has to have value 'yes'. Right now my query gets all records but I only want last 2. I tried to use LIMIT in each of the 4 subqueries and also tried OFFSET 0 and FETCH FIRST 2 ROWS ONLY but can't get it to work. If there is a way to use the OFFSET it could eliminate the need to use the comparison of date f2.delivered_on),0) + COALESCE( (SELECT sum(f3.rating2) FROM fruit f3 WHERE f1.name1 = f3.name2 AND f1.delivered_on > f3.delivered_on) ,0) AS fruit1_rating, COALESCE( (SELECT sum(f4.rating2) FROM fruit f4 WHERE f1.name2 = f4.name2 AND f1.delivered_on > f4.delivered_on),0) + COALESCE( (SELECT sum(f5.rating1) FROM fruit f5 WHERE f1.name2 = f5.name1 AND f1.delivered_on > f5.delivered_on) ,0) AS fruit2_rating FROM fruit f1; ``` Expected result: