Question 224956 in PhpMyAdmin Question posted in
The official documentation can be found here.

Phpmyadmin – SQL Search – Strange Behaviour getting date of max value

So I’m running a query against a MariaDB in PHPMyAdmin, its been working fine until we have got to a new month and for the life of me I can’t see the issue. The table is in the following format as an example, it contains data for both July and August so far:

timestamp generation export
2023-08-01 23:00:00 43.4 12.5

The SQL that’s working for for returning the MAX value for month 7 and year 2023;

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

This outputs exactly what I expect:

Fri 07 July 43.0

If I change the SQL to month number 8

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

I get the following despite there been data in the table;

MySQL returned an empty result set (i.e. zero rows)

If run the following query:

SELECT MAX(`generation`) FROM solar_generation WHERE MONTH(timestamp)= 8 AND YEAR(timestamp) = 2023;

I get the MAX generation as I’d expect for August, as soon as I start building the query for getting the data it stops returning data?

What is it I’m doing wrong, I just assumed the same query would work or is there a flaw in the query that is exposed by month 8 ?

Tags:

2

Answers


  1. 0

    I don’t use MariaDB.

    However, this looks suspicious:

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

    Basically, it says:

    • query table named solar_generation
    • find all rows in that table whose generation column value is equal to the largest generation value in the whole table
      • that’s most probably only one row that satisfies that condition, although there can be more of them that share the same (max!) generation column value
    • then, among all these rows (only one? A few of them?) fetch only those whose month is August (and year 2023)
      • apparently, as you didn’t get any result, the largest generation value doesn’t belong to August (2023)

    This is Oracle, but disregard that fact; it is just to illustrate what I think is going on:

    SQL> select * from solar_generation;

TIMESTAMP  GENERATION
---------- ----------
13.07.2023         20
28.07.2023         99  --> this is MAX generation; July 2023
14.08.2023         50
24.08.2023         80

SQL> select max(generation) from solar_generation;

MAX(GENERATION)
---------------
             99   --> OK, that's it

    Now, let’s select all rows that satisfy conditions: max generation and July:

    SQL> select * from solar_generation
  2  where generation = (select max(generation) from solar_generation) --> this is 99
  3    and extract(month from timestamp) = 7;

TIMESTAMP  GENERATION
---------- ----------
28.07.2023         99

    What about max generation and August?

    SQL> select * from solar_generation
  2  where generation = (select max(generation) from solar_generation) --> this is 99
  3    and extract(month from timestamp) = 8;

no rows selected

SQL>

    Right; there are no rows because generation = 99 belongs to July.

    I don’t know what result you expect as you didn’t explain it; if it is about max generation per month, then you’d do something like this (Oracle again; adjust it to MariaDB syntax):

    SQL> select to_char(timestamp, 'mm.yyyy') month,
  2    max(generation) max_generation
  3  from solar_generation
  4  group by to_char(timestamp, 'mm.yyyy');

MONTH   MAX_GENERATION
------- --------------
08.2023             80
07.2023             99

SQL>
    Login or Signup to reply.
  2. 0

    The answer provided by Littlefoot shows exactly where the problem is.

    The max value doesn’t belong to the August month.

    You need to make a little change to your logic . Find the max value per specific month and year.

    In your case it would be ,

    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
                     );

    I suggest using with clause at is more performant and easily to adapt if future changes happen,

    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

    See example

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search