Mysql GROUP BY

[Iris]

Hi,

I have a Mysql requet look like :
$query = "SELECT
$table.id,
DATE_FORMAT($table.date_fabrication, '%d-%m-%Y') AS date_fabrication,
$table.n_serie,
$table.industriel,
$table.mobilier,
$table.etat,
$table.zone,
$table.station,
$table.n_ligne,
DATE_FORMAT($table.date_visite, '%d-%m-%Y') AS date_visite,
$table.id_sma,
$table.heure_visite,
$table.etat_affichage
FROM $table ";

$query .= "GROUP BY CONCAT(DATE_FORMAT($table.date_fabrication, '%d-%m-%Y'), $table.n_serie, $table.industriel, $table.mobilier) 
			ORDER BY $trier_par $ordre_tri $table.date_fabrication DESC, $table.date_visite DESC, $table.heure_visite DESC";		

And I would like to print on the screen the last time (heure_visite) after I GROUP BY serial number (n_serie)

sample :
serial number | time
001 | 10:25:30
001 | 10:32:40
001 | 10:02:20
001 | 10:45:50

I would like this result !!! :
001 | 10:45:50

but right now I have this result ??? :
001 | 10:25:30

Thanks for your help...

[mikeatrpi]

$query .= "GROUP BY $table.n_serie, $table.industriel, $table.mobilier 
ORDER BY CONCAT(DATE_FORMAT($table.date_fabrication, '%d-%m-%Y') DESC, $trier_par 
$ordre_tri $table.date_fabrication DESC, $table.date_visite DESC, $table.heure_visite DESC"; 

I think I've got the right idea above but the wrong syntax. Group by your ID, and order by the date DESC.

[Iris]

Thanks...
but I think I found my way by:
$query = "SELECT
$table.id,
DATE_FORMAT($table.date_fabrication, '%d-%m-%Y') AS date_fabrication,
$table.n_serie,
$table.industriel,
$table.mobilier,
$table.etat,
$table.zone,
$table.station,
$table.n_ligne,
DATE_FORMAT($table.date_visite, '%d-%m-%Y') AS date_visite,
$table.id_sma,
MAX($table.heure_visite) AS heure_visite,
$table.etat_affichage
FROM $table ";

I added "MAX($table.heure_visite) AS heure_visite" and in that way I got the last time (heure) even if I GROUP BY serial number (n_serie)

[[deleted]]

" I added "MAX($table.heure_visite) AS heure_visite" and in that way I got the last time (heure) even if I GROUP BY serial number (n_serie)"

Remeber that this doesn't actually work.

If you use GROUP BY then you cannot select anything that you do not group by.

And you cannot use MAX() if you don't tell SQL whoch records it should count.

[Iris]

Thanks for your help...
But my query works fine with MAX and I have now the result I want, the last time of my GROUP BY serial number (n_serie)
;-)

[[deleted]]

"But my query works fine with MAX and I have now the result I want, the last time of my GROUP BY serial number (n_serie) "

Ofcourse it 'works', mysql does execute the query, but the results nake no sense.

Think about it, how can you select an item if that item is not in the GROUP BY?

GROUP BY means you only get results for records that have the same value for the columns mentioned in the GROUP BY.

That means that all other columns can have other values. Yet with GROUP BY you only get one record for each value of the columns mentioned in the GROUP BY. How does SQL know which of the values in the other columns it should display?

MAX with non-aggregates but without GROUP BY is also not possible, because MAX also works on a set of records and without GROUP BY there are no groups of records.

I suggest you read a few tutorials about SQL, because the results you get now are unreliable!

[Iris]

Vincente,
I didn't understand all what you said (sorry for my english !)
it's my query :

	$query = "SELECT 
		$table.id, 
		DATE_FORMAT($table.date_fabrication, '%d-%m-%Y') AS date_fabrication, 
		$table.n_serie,
		$table.industriel, 
		$table.mobilier, 
		$table.etat, 
		$table.zone, 
		$table.station, 
		$table.n_ligne, 
		DATE_FORMAT($table.date_visite, '%d-%m-%Y') AS date_visite, 
		$table.id_sma, 
		MAX($table.heure_visite) AS heure_visite, 
		$table.etat_affichage 
		FROM $table ";

$query .= "GROUP BY CONCAT(DATE_FORMAT($table.date_fabrication, '%d-%m-%Y'), $table.n_serie, $table.industriel, $table.mobilier) 
			ORDER BY $trier_par $ordre_tri $table.date_fabrication DESC, $table.date_visite DESC, $table.heure_visite DESC";		

So if I understood you I should add at the end of my GROUP BY line :
$table.heure_visite
like that :
$query .= "GROUP BY CONCAT(DATE_FORMAT($table.date_fabrication, '%d-%m-%Y'), $table.n_serie, $table.industriel, $table.mobilier), $table.heure_visite
ORDER BY $trier_par $ordre_tri $table.date_fabrication DESC, $table.date_visite DESC, $table.heure_visite DESC";

???