Get only result for most recent date on many to many relationship

Question

I have 3 tables :

Purchase requests - having columns - id, name

Order_purchase_request - having columns - order_id, purchase_request_id (Many to many link)

Orders - having columns - id, name, ordered_on

Purchase requests table
1| A 
2| B
3| C

Orders table
1| XYZ | 2020-10-28 00:00
2| PQR | 2020-10-27 00:00
3| ABC | 2020-10-29 00:00
4| DEF | 2020-10-29 00:00
5| GHI | 2020-10-30 00:00

Order_purchase_request 
1|1
2|1
3|3
4|2
5|2
5|3

I want the SQL query to return purchase requests with the highest order date

The result set should be something like -

purchase_request_id|Order Name|Ordered on
     1    |      XYZ      | 2020-10-28 00:00
     2    |      GHI      | 2020-10-30 00:00
     3    |      GHI      | 2020-10-30 00:00

I wrote the following SQL. But it's returning me all the rows. What could be wrong?

SELECT p.id, p.purchase_request_name, o.ordered_on
from purchase_requests p
         JOIN order_purchase_request opr
              on p.id = opr.purchase_request_id
         JOIN orders o on opr.order_id = o.id
WHERE o.id = (SELECT o.id
      FROM ???
      WHERE o.id = apr.amazon_purchase_order_id
      ORDER BY o.id DESC LIMIT 1)
ORDER BY purchase_request_name DESC;

I am using MariaDB.

Answer 1

You could use window functions:

SELECT *
FROM (
    SELECT p.id, p.purchase_request_name, o.ordered_on,
        RANK() OVER(PARTITION BY p.id ORDER BY o.ordered_on DESC) rn
    FROM purchase_requests p
    JOIN order_purchase_request opr ON p.id = opr.purchase_request_id
    JOIN orders o ON opr.order_id = o.id
) t
WHERE rn = 1
ORDER BY purchase_request_name DESC;