CODEWITHSUNDEEP
mysql
Giuseppe Lodi Rizzini
Giuseppe Lodi Rizzini

Reputation: 1055

Mysql query with GROUP BY and MAX, wrong result returned

I have strange results with my query:

SELECT 
  veicoli_ID, 
  MAX(DataScadenzaRevisione) AS mmax, 
  EsitoPositivo AS StatoUltimaRevisione 
FROM revisioni_veicolo 
GROUP BY veicoli_ID 
HAVING StatoUltimaRevisione = 1

I have create the fiddle here:

http://sqlfiddle.com/#!9/4d97cd/1

Why StatoUltimaRevisione column return always true? I think must be false for IDs 20 and 26.

EXPECTED (without: HAVING StatoUltimaRevisione = 1):

veicoli_ID     mmax     StatoUltimaRevisione
6           2019-08-01  true
7           2018-04-09  true
20          2018-12-01  false
26          2020-10-01  false
44          2019-09-01  true
45          2020-05-01  true

Upvotes: 0

Views: 59

Answers (3)

forpas
forpas

Reputation: 164079

The results you get are "correct" according to the documentation.
The logic you try to apply though, does not work.
You have in the SELECT list the column EsitoPositivo which is not included in the GROUP BY clause and it is not aggregated (with MIN, MAX, SUM etc) and you think that the value returned for the column EsitoPositivo will be the one from the row with the MAX(DataScadenzaRevisione), but this is not guaranteed.
Why? Because:

...the server is free to choose any value from each group, so unless they are the same, the values chosen are nondeterministic...

So the values chosen for EsitoPositivo is any value from the group it belongs to.

You must change this logic of your query by using a join of the table to a query that returns only veicoli_ID and MAX(DataScadenzaRevisione):

SELECT r.veicoli_ID, g.mmax, r.EsitoPositivo AS StatoUltimaRevisione 
FROM revisioni_veicolo r INNER JOIN (
  SELECT veicoli_ID, MAX(DataScadenzaRevisione) AS mmax
  FROM revisioni_veicolo
  GROUP BY veicoli_ID 
) g ON g.veicoli_ID = r.veicoli_ID AND g.mmax = r.DataScadenzaRevisione

or with NOT EXISTS: 

SELECT 
  r.veicoli_ID, 
  r.DataScadenzaRevisione AS mmax, 
  r.EsitoPositivo AS StatoUltimaRevisione 
FROM revisioni_veicolo r 
WHERE NOT EXISTS (
  SELECT 1 FROM revisioni_veicolo
  WHERE veicoli_ID = r.veicoli_ID AND DataScadenzaRevisione > r.DataScadenzaRevisione
)

See the demo.
Results:

> veicoli_ID | mmax       | StatoUltimaRevisione
> ---------: | :--------- | -------------------:
>          6 | 2019-08-01 |                    1
>          7 | 2018-04-09 |                    1
>         20 | 2018-12-01 |                    0
>         26 | 2020-10-01 |                    0
>         44 | 2019-09-01 |                    1
>         45 | 2020-05-01 |                    1

Upvotes: 1

Giuseppe Lodi Rizzini
Giuseppe Lodi Rizzini

Reputation: 1055

Found result:

 SELECT a.veicoli_ID, a.EsitoPositivo, a.DataScadenzaRevisione
FROM revisioni_veicolo a
INNER JOIN (
    SELECT veicoli_ID, MAX(DataScadenzaRevisione) AS MaxDataScadenzaRevisione
    FROM revisioni_veicolo
    GROUP BY veicoli_ID
) b ON a.veicoli_ID = b.veicoli_ID AND a.DataScadenzaRevisione = b.MaxDataScadenzaRevisione

Upvotes: 0

VBoka
VBoka

Reputation: 9083

Try this:

SELECT veicoli_ID
       , MAX(DataScadenzaRevisione) AS mmax
       , case when min(EsitoPositivo) = 0 then 'false'
         else 'true'
         end StatoUltimaRevisione
FROM revisioni_veicolo
GROUP BY veicoli_ID

DEMO

Upvotes: 0

Related Questions