How can the same function be provided without using the row_number() function in sqlite?

The query below is working perfectly well if the user’s phone supports sqlite version 3.25.0 or higher. Otherwise, as you can guess, the query doesn’t work. How can I transform the code, without using the row_number () function?

SELECT *
FROM
    (
        SELECT
            ROW_NUMBER() OVER (PARTITION BY recipe_name) AS rn,
            rt.*
        FROM
            SyncRecipeIngredientTable sr
            JOIN RecipeIngredientTable ri ON ri.recipe_ingredient_id = sr.recipe_ingredient_id
            JOIN RecipeTable rt ON rt.recipe_id = sr.recipe_id
        WHERE ri.recipe_item_name in ("patates", "soğan", "su")
        GROUP by rt.recipe_id
        HAVING COUNT(*) >= 3
    )
WHERE rn = 1

The database’s ER diagram:

The expected result:

Answer

I would expect recipes to have unique names. If so, then the outer quer is simply not needed:

SELECT rt.*
FROM SyncRecipeIngredientTable sr JOIN
     RecipeIngredientTable ri 
     ON ri.recipe_ingredient_id = sr.recipe_ingredient_id JOIN
     RecipeTable rt
     ON rt.recipe_id = sr.recipe_id
WHERE ri.recipe_item_name in ('patates', 'soğan', 'su')
GROUP by rt.recipe_id
HAVING COUNT(*) >= 3;

If recipes can have duplicated names, then you can use a correlated subquery:

WITH rt AS (
      SELECT rt.*
      FROM SyncRecipeIngredientTable sr JOIN
           RecipeIngredientTable ri 
           ON ri.recipe_ingredient_id = sr.recipe_ingredient_id JOIN
           RecipeTable rt
           ON rt.recipe_id = sr.recipe_id
      WHERE ri.recipe_item_name in ('patates', 'soğan', 'su')
      GROUP by rt.recipe_id
      HAVING COUNT(*) >= 3
     )
SELECT rt.*
FROM rt
WHERE rt.recipe_id = (SELECT MAX(rt2.recipe_id)
                      FROM rt rt2
                      WHERE rt2.recipe_name = rt.recipe_name
                     );