MAX(datetime) per user

ORDER BY id ASC (of DESC )
LIMIT 1

Resultaat van mijn query is dat hij alleen ID 7 laat zien, omdat die de laatste datum heeft.
Als ik je regel toevoeg, veranderd daar weinig aan. Stel dat ik meer records had met laatste datum 2013-02-05, dan laat hij met jou toegevoegde code ook nog alleen 1 record zien ipv alle gebruikers met hun laatste value.

Van bovenstaande kolom zou ik dus eigenlijk de volgende values willen zien:

je moet de limit eruit halen. limit geeft maar 1 waarde terug namelijk

Reshad F op 23/03/2013 22:13:26

je moet de limit eruit halen. limit geeft maar 1 waarde terug namelijk

Dit is mijn query die dus niet goed is, maar daar heb ik toch geen LIMIT in?!?
SELECT r.`matchdate`, r.`value` as `value`, r.`user`, u.`status`, u.`id`, u.`name`
FROM `userrating` r, `users` u
WHERE r.`user` = u.`id`
AND u.`status` = '1'
AND matchdate= (SELECT MAX(matchdate) FROM userrating)
GROUP BY r.`user`

[size=xsmall]Toevoeging op 23/03/2013 22:31:41:[/size]

Okay, ik had het laten rusten even omdat ik er niet uit kwam. Nu toch weer naar gekeken en iets met php knipper zijn reactie gedaan. de volgende query werkt:

SELECT r.`matchdate`, r.`value` as `value`, r.`user`, u.`status`, u.`id`, u.`name`
FROM `userrating` r, `users` u
WHERE r.`user` = u.`id`
AND u.`status` = '1'
ORDER BY r.`matchdate` DESC LIMIT 12

(desc limit 12 toegevoegd omdat ik er 12 moet hebben, al zijn er ook 12 gebruikers op basis van selectie wie status 1 heeft)

SELECT * 
FROM (
	SELECT * 
	FROM `userrating` 
	WHERE `status` =1
	ORDER BY `user` , `matchdate` DESC 
) AS t
GROUP BY `user` 

GROUP BY moet in combinatie met een aggegrate functie, en helemaal volgens de regels moeten alle kolommen in de SELECT die niet in een aggegrate staan ook in de GROUP BY.
Dus ook niet helemaal correct:

SELECT MAX(r.`matchdate`) maxdate, r.`value` as `value`,
	r.`user`, u.`status`, u.`id`, u.`name` 
FROM `userrating` r, `users` u
WHERE r.`user` = u.`id`
AND u.`status` = '1'
GROUP BY r.`user`
HAVING r.matchdate = maxdate

De nette methode:

SELECT
	ur.maxdate, r.`value`, ur.user_id, u.name, u.status
FROM
	(SELECT
		MAX(matchdate) maxdate, `user` user_id
	FROM
		userrating
	GROUP BY
		user_id) ur
JOIN
	userrating r
	ON  ur.user_id = r.`user`
	AND ur.maxdate = r.matchdate
JOIN
	users u
	ON ur.user_id = u.id
WHERE
	u.status = 1
ORDER BY
	ur.maxdate DESC
LIMIT 12

Ger ik heb mijn ene tabel omgebogen naar twee tabellen om dit na te bootsen.
Jouw subquery (regel 4 tot 10) geeft de volgende error:

#1054 - Unknown column 'matchdate' in 'having clause'

als ik hem dan een kolom 'matchdate' meegeef dan is de query geldig maar ik krijg niet de laatste datums.

SELECT
    matchdate, MAX(matchdate) maxdate, `user` user_id
FROM
    userrating
GROUP BY
    user_id
HAVING matchdate = maxdate

Ok, de query van Frank van gisteravond werkt iig. Nu kijken hoe die query van Ger in elkaar steekt...
Bedankt iig voor de reacties

@Frank, klopt was nog niet helemaal wakker, maar de gehele HAVING clause is in de laatste query overbodig, omdat er een SELF JOIN op de maxdate en user_id plaats vindt.
Het feit dat jouw query werkt is dat het mysql is én dat de full group by optie uit staat.

aha Ger ik zie dat je hem aangepast hebt.
Ik ga er eens kritisch naar kijken, dank je wel!

[size=xsmall]Toevoeging op 24/03/2013 13:02:27:[/size]

Ger, ik probeer voor mezelf te verwoorden waarom een self-join?

antwoord:
omdat ik eerst GROUP BY wil toepassen en dan pas ORDER BY,
en omdat dit een één query niet mogelijk is want daar is het altijd eerst ORDER BY en dan pas GROUP by

zit ik zo op de juiste weg of zie ik het verkeerd?