Ordina nel gruppo da?

Nel mio sistema, ho clienti. I clienti hanno programmi. Voglio visualizzare un elenco di client, mostrando il loro programma attivo più recente (se esiste).

Quindi, abbiamo qualcosa di simile a questo:

SELECT * FROM clients AS client JOIN programs AS program ON client.id=program.client_id GROUP BY client.id ORDER BY program.close_date=0 DESC, program.close_date DESC 

close_date=0 significa che il programma non è chiuso. Quindi metterà prima i programmi non chiusi e poi quelli chiusi più di recente.

Il problema è che l’ordine non funziona all’interno dei gruppi. È solo una specie di scegliere uno dei programmi a caso. Come posso risolvere questo?


Ho appena trovato questo:

 SELECT * FROM clients AS client JOIN (SELECT * FROM programs AS program ORDER BY program.close_date=0 DESC, program.close_date DESC) AS program ON client.id=program.client_id GROUP BY client.id 

Che sembra dare risultati corretti. È corretto, o sto solo diventando fortunato? cioè, ho essenzialmente ordinato la tabella prima di unirla; quei risultati rimarranno ordinati come fa il join, giusto?


Soluzione: ora credo che questo sia il classico problema massimo di gruppo . Cercalo se sei bloccato su un problema simile. La soluzione prevede di unire due volte lo stesso tavolo.

 SELECT c.*, p.* FROM clients AS c JOIN programs AS p ON p.id = ( SELECT pi.id FROM programs AS pi WHERE pi.client_id = c.id ORDER BY pi.close_date=0 DESC, pi.close_date DESC LIMIT 1 ) 

Grazie dovrebbe andare a @Quassnoi . Vedi la sua risposta in una domanda simile (ma più complicata): mysql-group-by-to-display-latest-result


Se aggiorni la tabella programs e imposta close_date per tutti i record che è zero per close_date='9999-12-31' , allora il tuo ORDER BY sarà più semplice (e l’intera query più veloce con gli indici appropriati):

  ORDER BY pi.close_date DESC 

Prova questo ordine per clausola …

 ORDER BY client.id, CASE WHEN program.close_date = 0 THEN 0 ELSE 1 END, program.close_date DESC