id | size | price | date
0 | 30 | 800 | 2021-10-01
1 | 30 | 900 | 2021-10-02
2 | 32 | 700 | 2021-09-11
3 | 30 | 800 | 2021-09-21
4 | 32 | 800 | 2021-09-01
5 | 32 | 0 | 2021-10-03
SELECT *
FROM (SELECT *
FROM `prices`
WHERE model_id = '269'
AND partner_id = '0'
AND size <= '32'
AND date_time <= '2021-10-19'
ORDER BY size DESC, date_time DESC
) AS t_1
GROUP BY size
LIMIT 1
id | size | price | date
5 | 32 | 0 | 2021-10-03
2 | 32 | 700 | 2021-09-11
4 | 32 | 800 | 2021-09-01
1 | 30 | 900 | 2021-10-02
0 | 30 | 800 | 2021-10-01
3 | 30 | 800 | 2021-09-21
id | size | price | date
5 | 32 | 0 | 2021-10-03
1 | 30 | 900 | 2021-10-02
model_id = '269'
AND partner_id = '0'
AND size <= '32'
SELECT id, size, price, date
FROM prices p
WHERE model_id = 269
AND partner_id = 0
AND date_time <= '2021-10-19'
AND (select count(*)
from prices p2
where -- сюда нужно прописать параметры строк, по которым будет определяться окно счетчика
-- то есть, отсчет счетчика будет в пределах одного размера, одной и той же модели, одного и того де партнера.
p2.size = p.size
and p2.partner_id = p.partner_id
and p2.model_id = p.model_id
-- а тут параметр, чем отличается одна строка счетчика от другой
and p2.date > p.date
) < 1 -- сколько строк отсечь от категории (n + 1) ?
order by p.date desc
SELECT
prices.*
FROM
`prices`
JOIN (
SELECT size, MAX(date_time) date_time FROM prices GROUP BY size
) last_price USING (size, date_time)
WHERE
-- model_id = '269' AND partner_id = '0' AND
size <= '32'
AND date_time <= '2021-10-19'
AND price > 0
ORDER BY
size DESC,
date_time DESC;
SELECT * FROM (
SELECT
prices.*,
ROW_NUMBER() OVER (PARTITION BY size ORDER BY date_time DESC) last_price
FROM
`prices`
WHERE
-- model_id = '269' AND partner_id = '0' AND
size <= '32'
AND date_time <= '2021-10-19'
) data
WHERE last_price = 1 AND price > 0
ORDER BY
size DESC;
WITH `cte` AS (
SELECT *, ROW_NUMBER() OVER `w` AS `row_num`
FROM `table`
WHERE `size` <= :size
WINDOW `w` AS (
PARTITION BY `size`
ORDER BY `date` DESC
)
) SELECT *
FROM `cte`
WHERE `row_num` = 1
ORDER BY `price` = 0, `size` DESC
LIMIT 1
SELECT `t`.*
FROM (
SELECT `size`, MAX(`date`) as `date`
FROM `table`
WHERE `size` <= 32
GROUP BY `size`
) AS `m`
JOIN `table` AS `t`
ON `t`.`size` = `m`.`size` AND `t`.`date` = `m`.`date`
ORDER BY `t`.`price` = 0, `t`.`size` DESC
LIMIT 1
SELECT prices.*
FROM prices
JOIN ( SELECT size, MAX(`date`) AS `date`
FROM prices
WHERE price
-- AND `date` <= @some_date
GROUP BY size ) AS last_nonzero_date USING (size, `date`);
(size, `date`)
- уникально.