MAX(datetime) per user
Ik wil een query uitvoeren die de gebruikers ophaalt met hun value, die status 1 hebben, maar dan wel hun laatst bestaande value.
De volgende query heb ik, maar nu zoekt hij de laatste datum, en daarbij de gebruikers die die datum hebben en met status 1. (ps. status van bovenstaande tabel komt uit andere tabel, maar even voor gemak bij gezet.
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`
Hoe krijg ik dus alle gebruikers die status 1 hebben, en vervolgens met hun laatst "behaalde" value.
Gewijzigd op 23/03/2013 21:10:07 door Jochanan V
LIMIT 1
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:
Gewijzigd op 23/03/2013 21:58:33 door Jochanan V
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`
Toevoeging op 23/03/2013 22:31:41:
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)
Code (php)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
SELECT *
FROM (
SELECT *
FROM `userrating`
WHERE `status` =1
ORDER BY `user` , `matchdate` DESC
) AS t
GROUP BY `user`
FROM (
SELECT *
FROM `userrating`
WHERE `status` =1
ORDER BY `user` , `matchdate` DESC
) AS t
GROUP BY `user`
Dus ook niet helemaal correct:
Code (php)
1
2
3
4
5
6
7
2
3
4
5
6
7
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
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:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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
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
Gewijzigd op 24/03/2013 12:12:31 door Ger van Steenderen
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.
Code (php)
1
2
3
4
5
6
7
2
3
4
5
6
7
SELECT
matchdate, MAX(matchdate) maxdate, `user` user_id
FROM
userrating
GROUP BY
user_id
HAVING matchdate = maxdate
matchdate, MAX(matchdate) maxdate, `user` user_id
FROM
userrating
GROUP BY
user_id
HAVING matchdate = maxdate
Bedankt iig voor de reacties
Het feit dat jouw query werkt is dat het mysql is én dat de full group by optie uit staat.
Ik ga er eens kritisch naar kijken, dank je wel!
Toevoeging op 24/03/2013 13:02:27:
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?