MySQL select N latest rows for each product from 3 relational tables

Question

Now i have this code which return latest record for each product. But i don't know how to modify this to get for example 3 latest rows for each product. I want to compare latest product prices and i need few latest rows of each.

shops

id | shopId
-----------
1  | 2345
2  | 6573

products

id | shopId | title    | active | pDateAdded          | pDateUpdate
---------------------------------------------------------------------------
18 |   1    | Honda    | 1      | 2021-03-07 01:56:34 | 2021-03-07 04:36:34
19 |   2    | Subaru   | 1      | 2021-03-07 03:43:34 | 2021-03-08 04:36:34
20 |   1    | VW       | 1      | 2021-03-07 07:21:34 | 2021-03-09 04:36:34
21 |   2    | Ford     | 0      | 2021-03-07 11:37:34 | 2021-03-10 04:36:34

prices

id  | shopId | productId | price | dDateAdded
-----------------------------------------------------
224 | 1      | 18       | 2385  | 2021-03-09 12:39:57
225 | 2      | 19       | 1523  | 2021-03-09 13:14:44
226 | 1      | 20       | 5489  | 2021-03-09 17:32:18
227 | 1      | 18       | 2256  | 2021-03-10 18:22:13
228 | 2      | 19       | 1600  | 2021-03-10 21:33:21
229 | 1      | 20       | 5321  | 2021-03-10 14:15:56
230 | 1      | 18       | 2137  | 2021-03-11 05:55:25
231 | 2      | 19       | 1666  | 2021-03-11 17:31:49
232 | 1      | 20       | 5001  | 2021-03-11 20:18:01

This command return only 1 latest record from prices table for every product from products table for specific shopId

SELECT s.*, c.*, d.*
FROM shops AS s
LEFT JOIN products AS c ON c.shopId = s.id
LEFT JOIN (
SELECT productId, MAX(dDateAdded) MaxDate
FROM prices
GROUP BY productId
) MaxDates
ON MaxDates.productId = c.id
LEFT JOIN prices AS d ON d.productId = c.id AND d.shopId = s.id AND MaxDates.MaxDate = d.dDateAdded
WHERE s.id = ".$shopId."

For example if shopId=1 this command get only that records (I omitted here the data from the other tables that are retrieved):

230 | 1      | 18       | 2137  | 2021-03-11 05:55:25
232 | 1      | 20       | 5001  | 2021-03-11 20:18:01

But i want to get for example 2 latest records for every product where shopId=1, so the records which i want to get:

(shops)id | (shops)shopId | title | active | price | dDateAdded
 1        | 2345          | Honda | 1      | 2256  | 2021-03-10 18:22:13
 1        | 2345          | Honda | 1      | 2137  | 2021-03-10 14:15:56
 1        | 2345          | VW    | 1      | 5321  | 2021-03-11 05:55:25
 1        | 2345          | VW    | 1      | 5001  | 2021-03-11 20:18:01

Answer 1

To select N latest rows needs to allocate row number and to filter by N rows. However, the ROW_NUMBER function is not supported in MySQL 5.7. So that you need to simulate the ROW_NUMBER function like the follwing:

You can get the desired result by adding subquery with row number to your query like the below:

DB Fiddle

SELECT
  s.id,
  s.shopId,
  c.title,
  c.active,
  d.price,
  d.dDateAdded
FROM shops AS s
LEFT JOIN products AS c ON c.shopId = s.id
LEFT JOIN prices AS d ON d.productId = c.id AND d.shopId = s.id
--
LEFT JOIN (
  SELECT
    p1.id,
    COUNT(p2.dDateAdded) + 1 row_num
  FROM prices p1 LEFT JOIN prices p2
  ON p1.shopId = p2.shopId AND
     p1.productId = p2.productId AND
     p1.dDateAdded < p2.dDateAdded
  GROUP BY p1.id, p1.shopId, p1.productId, p1.dDateAdded
) AS w
ON d.id=w.id
--
WHERE
  s.id = 1 AND
  w.row_num <= 2

DB Fiddle

SELECT
  id,
  shopId,
  productId,
  price,
  dDateAdded
FROM (
  SELECT p1.*,
    (
      SELECT COUNT(*)+1 FROM prices p2
      WHERE
        p1.shopId = p2.shopId AND
        p1.productId = p2.productId AND
        p1.dDateAdded < p2.dDateAdded
    ) row_num
  FROM prices p1
) p
WHERE
  shopId = 1 AND
  row_num <= 2
ORDER BY id

DB Fiddle

SELECT p.* FROM prices p
INNER JOIN (
  SELECT
    p1.id,
    COUNT(p2.dDateAdded) + 1 row_num
  FROM prices p1 LEFT JOIN prices p2
  ON p1.shopId = p2.shopId AND
     p1.productId = p2.productId AND
     p1.dDateAdded < p2.dDateAdded
  GROUP BY
    p1.id,
    p1.shopId,
    p1.productId,
    p1.dDateAdded
) w
ON p.id=w.id
WHERE
  p.shopId = 1 AND
  w.row_num <= 2
ORDER BY p.id

Other way using a variable