MySql: Find nearest date for each product subtype within a type

Question

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.

Answer 1

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   |

---

View on DB Fiddle