[MySql] Select (max) che funziona a metà

[Sonikag]

Ciao. Ho tre tabelle

clienti:
idcliente
nomecliente
idlocalita

localita:
idlocalita
localita
provincia

movimenti:
idmovimento
idcliente
data
tipomovimento
note

Ovviamente per un cliente posso avere più movimenti.
Partendo da una località vorrei sapere l'ultimo movimento di un cliente e quindi ho fatto così:

SELECT max(movimenti.data) as datamov, clienti.idcliente, movimenti.note  as notemov, clienti.cliente, clienti.stato, localita.localita AS  loccli, localita.provincia AS procli  FROM `clienti` INNER JOIN localita  ON clienti.idlocalita = localita.idlocalita INNER JOIN movimenti ON  clienti.idcliente = movimenti.idcliente where clienti.idlocalita='8'   group by clienti.cliente order by provincia, localita

Il risultato è corretto al 80%!

Se il cliente ha un solo movimento il risultato è corretto!
Se il cliente ha più movimenti mi trova si la data massima corretta, ma mi fa vedere la nota di un altro movimento, mentre invece io devo visualizare la nota relativa alla riga di max(movimenti.data) che calcola correttamente.

Help!

[optime]

ok, facciamo un esempio

ID DATA
1 2021-02-23
2 2021-02-23

Quale dei due deve prendere?

[Sonikag]

Grazie!!!
mmm se la data è identica quello con id più alto. Nel mio caso però una data identica è quasi impossibile.

Nel mio caso (tralasciamo le località momentaneamente per semplificare) il cliente con id 5 ha questi movimenti:

idmov idcli data tipo note
1; 5; 2021-02-23;visita; lasciato preventivo
1; 5; 2021-01-20;visita; parlato con Antonio
1; 5; 2020-12-13;telef; preso appuntamento

Se faccio una ricerca con quel cliente il risultato è che maxdata (correttamente) è 2021-02-23, ma come "note" mi visualizza "preso appuntamento" oppure "parlato con Antonio", mentre io voglio la nota relativa a max(data)

[optime]

Confronta queste due query

SELECT Max(data),       tipo,
       note
FROM   table1
GROUP  BY idcli

SELECT *
FROM   table1 T1
       JOIN (SELECT Max(data) AS Data,
                    idcli
             FROM   table1
             GROUP  BY idcli) T2
         ON T1.data = T2.data
            AND T1.idcli = T2.idcli

http://sqlfiddle.com/#!9/da1ae4d/5

[Sonikag]

Funziona! anche se la logia non mi è chiarissima...
Ho visto la comparazione, ma nel primo caso (quello che avevo usato) non capisco perchè azzecca il max data e non il resto Però il risultato torna con il mio che non funzionava.

Nel tuo caso in pratica prima prendo tutti i movimenti e faccio il join solo del maxdata per ogni cliente, ho capito bene?
Io l'ho adattata come sotto... facendo i join alle altre due tabelle. Il risultato è corretto, mi confermi anche la logica?

SELECT * FROM movimenti T1
JOIN
(SELECT Max(data) AS Data, movimenti.idcliente, clienti.cliente, localita.localita, localita.provincia 
FROM movimenti INNER JOIN clienti ON movimenti.idcliente = clienti.idcliente 
INNER JOIN localita on clienti.idlocalita = localita.idlocalita 
WHERE movimenti.idcliente='2666' GROUP BY idcliente) T2 ON T1.data = T2.data AND T1.idcliente = T2.idcliente

Grazie per il preziosissimo aiuto.

[optime]

che la prima non funzioni è un effetto collaterale della possibilità offerta da MySQL di scrivere query non formalmente corrette (per SQL standard, tipo e note dovrebbero stare nella GROUP BY, ma ovviamente otterresti un risultato diverso e comunque non adatto alla tua situazione) cfr http://sqlfiddle.com/#!18/bd5bf/2

per la seconda il ragionamento è corretto

[optime]

prova anche a mettere

movimenti.idcliente='2666'

nella JOIN, potresti ottenere prestazioni migliori

[Sonikag]

nella join dove?

Nella realtà quel dato era per prova, la query è finita così:

SELECT * FROM movimenti T1 JOIN (SELECT Max(data) AS Data, movimenti.idcliente, clienti.cliente,  clienti.stato, localita.localita, localita.provincia FROM movimenti  INNER JOIN clienti ON movimenti.idcliente = clienti.idcliente INNER JOIN  localita on clienti.idlocalita = localita.idlocalita  WHERE idutente=1  and cliente like '%%'  and clienti.idlocalita='8'  GROUP BY idcliente)  T2 ON T1.data = T2.data AND T1.idcliente = T2.idcliente

dove:
idutente=1 // questo c'è sempre, pende l'id in sessione dell'utente collegato e mostra solo i suoi dati
cliente like '%%' // questo se qualcuno mi mette il nome o parte del nome cliente che vuole cercare
clienti.idlocalita='8' // questo o metto l'id di una località in alternativa la provincia selezionata così and localita.provincia='MO'

[optime]

@Sonikag, formatta la query, pls...

[Sonikag]

SELECT * FROM movimenti T1 
JOIN (SELECT Max(data) AS Data, movimenti.idcliente, clienti.cliente,  clienti.stato, localita.localita, localita.provincia 
   FROM movimenti 
   INNER JOIN clienti ON movimenti.idcliente = clienti.idcliente 
    INNER JOIN  localita on clienti.idlocalita = localita.idlocalita  
    WHERE idutente=1  and cliente like '%%'  and clienti.idlocalita='8'  GROUP BY idcliente) 
   T2 ON T1.data = T2.data AND T1.idcliente = T2.idcliente

Ho ancora qualche perplessità mi sa che questa notte mi hai dato da pensare. Funziona, ma se si ottimizza meglio ancora!
Grazie