I apologize if this has been asked before, but I couldn't find an identical problem in StackOverflow.
I have a table named prices, like this, where type, subtype and date are primary keys:
type subtype date price
18 | DFY | 2019-06-27 | 230
18 | DFY | 2019-06-28 | 241
18 | RGY | 2019-06-28 | 234
23 | NDO | 2019-06-26 | 227
23 | NDO | 2019-06-27 | 241
23 | SOG | 2019-06-26 | 235
23 | SOG | 2019-06-27 | 239
23 | SOG | 2019-06-28 | 292
23 | SOG | 2019-06-29 | 238
23 | SOG | 2019-07-02 | 236
For a given type, I need to retrieve at most one row for each of its subtypes, where date is the nearest to a given date, being at most 5 days before, but not after, the given date.
For example, for the type 23 and the given date being 2019-06-30, the expected result is these two rows:
23 NDO 2019-06-27 241
23 SOG 2019-06-29 238
I tried this:
select * from
(select * from
(select t.*, datediff('2019-06-30', t.date) as difference
from prices t
where t.type = 23
and t.date < '2019-06-30'
having difference <= 5
) temp
order by temp.difference
) temp2
group by temp2.subtype
However, the two returned rows are not the ones with the least difference.
It has to be all in a single statement because of the way I designed my data access code.
Schema (MySQL v5.7)
CREATE TABLE my_table (
`type` INTEGER,
`subtype` VARCHAR(3),
`date` date,
`price` INTEGER,
PRIMARY KEY(type,subtype,date)
);
INSERT INTO my_table
(`type`, `subtype`, `date`, `price`)
VALUES
('18', 'DFY', '2019-06-27', '230'),
('18', 'DFY', '2019-06-28', '241'),
('18', 'RGY', '2019-06-28', '234'),
('23', 'NDO', '2019-06-26', '227'),
('23', 'NDO', '2019-06-27', '241'),
('23', 'SOG', '2019-06-26', '235'),
('23', 'SOG', '2019-06-27', '239'),
('23', 'SOG', '2019-06-28', '292'),
('23', 'SOG', '2019-06-29', '238'),
('23', 'SOG', '2019-07-02', '236');
Query #1
SELECT a.*
FROM my_table a
JOIN
( SELECT type
, subtype
, MAX(date) date
FROM my_table
WHERE date BETWEEN '2019-06-30' - INTERVAL 5 DAY AND '2019-06-30'
GROUP
BY type
, subtype
) b
ON b.type = a.type
AND b.subtype = a.subtype
AND b.date = a.date;
| type | subtype | date | price |
| ---- | ------- | ---------- | ----- |
| 18 | DFY | 2019-06-28 | 241 |
| 18 | RGY | 2019-06-28 | 234 |
| 23 | NDO | 2019-06-27 | 241 |
| 23 | SOG | 2019-06-29 | 238 |