MariaDB 10.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE solar_generation (
timestamp timestamp,
generation decimal (5,2) ,
export decimal (5,2) );

insert into solar_generation  values
('2023-08-01 23:00:00',43.4,12.5),
('2023-08-02 23:00:00',40.4,12.5),
('2023-07-01 23:00:00',46.4,12.5),
('2023-07-02 23:00:00',40.4,12.5);




Records: 4  Duplicates: 0  Warnings: 0


select * from solar_generation;


timestamp
generation
export


2023-08-01 23:00:00
43.40
12.50

2023-08-02 23:00:00
40.40
12.50

2023-07-01 23:00:00
46.40
12.50

2023-07-02 23:00:00
40.40
12.50


SELECT DATE_FORMAT(`timestamp`, '%a %d %M') AS timestamp,
      `generation` 
FROM solar_generation
WHERE `generation` = (SELECT MAX(`generation`) 
                      FROM solar_generation where MONTH(timestamp)= 8 
                      AND YEAR(timestamp) = 2023
                     );


timestamp
generation


Tue 01 August
43.40


with max_val_per_month as (
  select `timestamp`,
          `generation` ,
        row_number() over(partition by MONTH(timestamp),YEAR(timestamp) order by generation desc ) max_gen
  from solar_generation
  ) select  DATE_FORMAT(`timestamp`, '%a %d %M') AS timestamp,
            `generation` 
    from max_val_per_month
    where max_gen = 1 
    and  MONTH(timestamp) = 8
    and year(timestamp) = 2023


timestamp
generation


Tue 01 August
43.40

timestamp	generation	export
2023-08-01 23:00:00	43.40	12.50
2023-08-02 23:00:00	40.40	12.50
2023-07-01 23:00:00	46.40	12.50
2023-07-02 23:00:00	40.40	12.50