Empty result of a query with 'group by' and 'having' clause

Question

I have two tables: osn

+----+---------------------------------+--------+
| id | decimal_num      | eri          | devices|
+----+---------------------------------+--------+
|  1 | AD2S80AUD        | AD2S80AUD    | 419    |
|  2 | AD2S99           | AD2S99       | 419    |
|  3 | F2K_14pin        | 14pin        | F2K    |
+----+---------------------------------+--------+

and osn_check:

+----+--------+------------+------------+-------+------------+------+---------+
| id | osn_id | check_date | check_type | works | conclusion | fio  | comment |
+----+--------+------------+------------+-------+------------+------+---------+
|  2 |      1 | 2022-04-29 |          1 |       | NULL       | NULL | NULL    |
|  4 |      1 | 2023-05-24 |          0 | NULL  | NULL       | NULL | NULL    |
+----+--------+------------+------------+-------+------------+------+---------+

I need to select the fields from osn and osn_check where osn_check.check_date is maximal for each group. I execute this query:

select decimal_num, eri, check_date, 
  concat(date_format(check_date, '%d.%m.%Y'), ' - ', 
  date_format(date_add(check_date, interval 1 year), '%d.%m.%Y')) 
from osn join osn_check on osn_check.osn_id=osn.id 
group by osn.id 
having check_date=max(check_date);

The result is empty. My desired result would be like this:

AD2S80AUD | AD2S80AUD | 2023-05-24 | 24.05.2023 - 24.05.2024

How can I do this?

The non-aggregated `check_date` comes from any random row in the group. If it doesn't happen to be the one with the max value, you won't get a result. — Barmar, May 24 '23 at 20:18

score 0 · Accepted Answer · answered May 24 '23 at 20:20

Basically you want filtering rather than aggregation. In the spirit of your initial attempt, we could use a correlated subquery to return the latest date of each item, so:

select o.*, oc.check_date, oc.check_type
from osn o
inner join osn_check oc on oc.osn_id = o.id
where oc.check_date = (
    select max(oc1.check_date)
    from osn_check oc1
    where oc1.osn_id = o.id
)

A more modern approach uses row_number() (MySQL >= 8.0):

select o.*, oc.check_date, oc.check_type
from osn o
inner join (
    select oc.*,
        row_number() over(partition by osn_id order by check_date desc) rn
    from osn_check oc
) oc on oc.osn_id = o.id
where oc.rn = 1

If there are many rows in the check table and few rows in the reference table, maybe a lateral join would be more efficient (requires MySQL >= 8.0.14):

select o.*, oc.check_date, oc.check_type
from osn o
cross join lateral (
    select oc.*
    from osn_check oc
    where oc.osn_id = o.id
    order by check_date desc limit 1
) oc

For performance, consider an index on osn_check(osn_id, check_date desc), so the lateral subquery can run fast.

Empty result of a query with 'group by' and 'having' clause

1 Answers1