Select Records Based On The Largest Value Of A Field By Two Join

[FedericoBiccheddu]

edit_band 	edit_user 	edit_time 	edit_block 	edit_reason1 	 		2 	 	1248635164 	2			  Prova Motivo1 	 		2 	 	1248635285 	2 	 		Prova Motivo 22 	 		3 	 	1250939034 	1			  Prova Motivo 31 	 		2 	 	1250978390 	41 	 		2 	 	1250984273 	41 	 		2 	 	1250984277 	41 	 		2 	 	1250984314 	41 	 		2 	 	1250984317 	41 	 		2 	 	1250984351 	41 	 		2 	 	1250984361 	41 	 		2 	 	1250984533 	4

As you can see the first field has many duplicate values. Now, I have to take the row for each unique ID in edit_band which has the edit_time greater.The problem is that I take the data, but I melee them all.The query I use now is this:

SELECT band.band_id, band.band_name, edit.*, user.username, COUNT(edit.edit_band) AS totalFROM ip_bands AS bandRIGHT JOIN ip_bands_edits AS edit ON edit.edit_band = band.band_idLEFT JOIN ip_users AS user ON edit.edit_user = user.user_idGROUP BY edit.edit_bandORDER BY edit.edit_time DESC LIMIT 0, 10

I wrong section, sorry! You could move it to SQL?

Edited September 30, 2009 by FedericoBiccheddu

[justsomeguy]

You can use this to get a list of IDs with the max time for each:SELECT edit_band, MAX(edit_time) FROM edit GROUP BY edit_bandOnce you get those records, you can use the values in your other query to get the other data. I don't think you can get all of that data in one query, I think first you need to figure out what the max value of the time is for each edit band, and then use another query to get the rest of the details.