MySQL LIMIT ignored in correlated subquery

I have 2 tables: orders and items. Each order has many items. Each item has a type. I want a query that returns all orders where the MOST RECENT item has type = 2

I can build a correlated EXISTS clause that filters to orders where ANY of the items has type=2

SELECT * FROM orders 
WHERE EXISTS(
    SELECT NULL 
    FROM items 
    WHERE order_id = orders.id
    AND type=2 
    ORDER BY id DESC 
    LIMIT 1
)

but for the life of me, I can't seem to formulate a query to filter orders to those whose MOST RECENT item has type=2

I've also tried every combination of JOIN to attempt to overcome the LIMIT clause being ignored. Nothing works.

I feel like this should be simple.

Solution

You may not need to use Exists() clause. You can directly compare the result of correlated subquery to a value (2) in the Where. I am assuming the definition of "FIRST item" from your attemped query (ORDER BY id DESC LIMIT 1). Try the following query:

SELECT o.* 
FROM orders AS o 
WHERE 2 = (SELECT type 
           FROM items 
           WHERE order_id = o.id 
           ORDER BY id DESC LIMIT 1)

Alternatively, you can use the Correlated Subquery in the Select clause itself, to get the first item type value for an order.
You can then use this result in a Derived table and filter out the cases where first item type = 2.

Try the following:

SELECT dt.* 
FROM 
(
 SELECT o.*, 
        (SELECT type 
         FROM items 
         WHERE order_id = o.id 
         ORDER BY id DESC LIMIT 1) AS first_item_type
 FROM orders AS o 
) AS dt 
WHERE dt.first_item_type = 2