Jump to content

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


Recommended Posts

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 by FedericoBiccheddu
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...