How to select minimal (maximal) of each matched rows in one to many relation?

Question

I have three tables, e.g. fruits:

+----+--------+---------+
| id | type   | variety |
+----+--------+---------+
|  1 | orange |     5   |
|  2 | orange |     7   |
|  3 | apple  |     1   |
|  4 | apple  |     0   |
+----+--------+---------+

containers:

+----+--------+
| id | year   | 
+----+--------+
|  1 | 2015   |
|  2 | 2020   |
|  3 | 2020   |
|  4 | 2018   |
+----+--------+

and inclusion:

+----+----------+---------+
| id | fruit_id | cont_id |
+----+----------+---------+
|  1 |      1   |     1   |
|  2 |      1   |     2   |
|  3 |      2   |     1   |
|  4 |      3   |     2   |
|  5 |      3   |     3   |
|  6 |      3   |     4   |
+----+----------+---------+

I need to select "newest" container for each fruit variety if there is any:

+----+--------+----------+------+
| id | type   | variety  | year |
+----+--------+----------+------+
|  1 | orange |    5     | 2020 |
|  2 | orange |    7     | 2015 |
|  3 | apple  |    1     | 2020 |
|  4 | apple  |    0     | NULL |
+----+--------+----------+------+

I'm trying something like

SELECT * FROM `fruits`
LEFT JOIN (SELECT * FROM `containers` 
           JOIN `inclusion` ON `inclusion`.`cont_id` = `containers`.`id` 
           WHERE `fruit_id` = `fruits`.`id` 
           ORDER BY `year` DESC LIMIT 1
          ) `tops` ON `tops`.`fruit_id` = `fruits`.`id`;

but it says

ERROR 1054 (42S22): Unknown column 'fruits.id' in 'where clause'

is there any way to get the required result?

I'm using mariadb my now, but migration to mysql could happen, so I need a solution working on both servers.

What if I also add cnt_type table:

+----+---------+
| id | type    |
+----+---------+
|  1 | box     |
|  2 | package |
+----+---------+

and containers would include type:

+----+--------+------+
| id | year   | type |
+----+--------+------+
|  1 | 2015   | 1    |
|  2 | 2020   | 1    |
|  3 | 2020   | 2    |
|  4 | 2018   | 2    |
+----+--------+------+

so I need to extract top-year of each container type including each fruit variety?

+----+--------+----------+----------+------+
| id | type   | variety  | cnt_type | year |
+----+--------+----------+----------+------+
|  1 | orange |    5     | box      | 2020 |
|  1 | orange |    5     | package  | NULL |
|  2 | orange |    7     | box      | 2015 |
|  2 | orange |    7     | package  | NULL |
|  3 | apple  |    1     | box      | 2020 |
|  3 | apple  |    1     | package  | 2020 |
|  4 | apple  |    0     | box      | NULL |
|  4 | apple  |    0     | package  | NULL |
+----+--------+----------+----------+------+

In this case combination type-year for each container should be unique.

Maria is latest version, 10+, mysql not sure, but should be 5.7+ — Nick, Jan 30 '21 at 22:09
If you could be specific it would be great.. This is quite easy witn window functions but theyre maria 10.2+ (there is no Mysql 7) — Caius Jard, Jan 30 '21 at 22:11
Did one of these answers solve your problem? If not, could you provide more information to help answer it? — Nick, Feb 02 '21 at 23:27

Barbaros Özhan · Answer 1 · 2021-01-30T22:45:52.593

You can use window functions such as DENSE_RANK() for MariaDB 10.2+ in order to pick the latest records grouped by fruit id's as follows

WITH f AS
(
SELECT f.*, c.year, DENSE_RANK() OVER (PARTITION BY f.id ORDER BY c.year DESC) AS dr
  FROM fruits f
  LEFT JOIN inclusion i
    ON i.fruit_id = f.id
  LEFT JOIN containers c
    ON c.id = i.cont_id
)
SELECT id , type, variety, year
  FROM f
 WHERE dr = 1

Demo

Update : If you need to expand the results as adding that table(cnt_type) in the last update, then replace the query with the following one

WITH f AS
(
SELECT f.*, cn.type AS cnt_type, c.year,
       DENSE_RANK() OVER (PARTITION BY f.type, f.variety, cn.type ORDER BY c.year DESC) 
    AS dr
  FROM fruits f
  LEFT JOIN inclusion i
    ON i.fruit_id = f.id
 CROSS JOIN cnt_type cn    
  LEFT JOIN containers c
    ON c.id = i.cont_id AND c.type = cn.id
)
SELECT id , type, variety, cnt_type, year
  FROM f
 WHERE dr = 1
 ORDER BY id, type, variety, cnt_type

Demo

where returning rows are multiplexed due to each rows of cnt_type table through use of CROSS JOIN.

Caius Jard · Answer 2 · 2021-01-30T22:28:00.893

In Maria 10.2+ and MySQL 8+ window functions can solve this:

WITH x AS (
SELECT 
  *, 
  ROW_NUMBER() OVER(PARTITION BY f.type, f.variety, ct.cnt_type ORDER BY c.year DESC) rn
FROM 
  fruits f
  INNER JOIN inclusion i on f.id = i.fruit_id
  LEFT JOIN containers c on c.id = i.container_id
  LEFT JOIN cnt_type ct on c.type = ct.id
)
SELECT * FROM x WHERE rn = 1

I'm not entirely sure I got your requirement on the last line, because you have multiple things called "type" and you talked about fruit types and varieties in the first instance, then you stoped talking about varieties in the second instance.. but you can change the columns in the PARTITION BY if you need. Easiest way to "debug" the requirement is to remove the WHERE rn = 1 and just run the query, then you'll see the rn column having 2, 3, 4 maybe.. If you adjust the PARTITION BY it will behave like the rows are being counted up and everything in the partition that is the same will have a counter that increments as the years descend (so for my query with a partition of f.type, f.variety, ct.cnt_type if you have 5 rows where all those rows have the same values for f.type, f.variety, ct.cnt_type, then the rn will increment from 1..5 as the years descend. If it's wrong and only f.type, f.variety should be considered "the group within which the rn counts up" then make the partition that instead

Because the most recent year is the one where rn = 1 that's what we pick to give the latest

score 1 · Answer 3 · answered Jan 30 '21 at 22:35

If you end up stuck with MySQL 5.7 (and no window functions) you can use this query (which will also work on MariaDB). The problem can be resolved to a fairly simple selection of MAX(containers.year) grouped by all columns in fruit and the container type. Note a CROSS JOIN of fruits to cnt_type is required to ensure that all fruit/container combinations are included in the output:

SELECT f.id, f.type, f.variety, 
       ct.type AS cnt_type,
       MAX(c.year) AS year
FROM fruits f
CROSS JOIN cnt_type ct
LEFT JOIN inclusion i ON i.fruit_id = f.id
LEFT JOIN containers c ON c.id = i.cont_id AND c.type = ct.id
GROUP BY f.id, f.type, f.variety, ct.type

Output:

id  type    variety     cnt_type    year
1   orange  5           box         2020
1   orange  5           package     null
2   orange  7           box         2015
2   orange  7           package     null
3   apple   1           box         2020
3   apple   1           package     2020
4   apple   0           box         null
4   apple   0           package     null

Demo on db-fiddle

How to select minimal (maximal) of each matched rows in one to many relation?

3 Answers3