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
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:
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
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
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