CODEWITHSUNDEEP
mysqlsqlgroup-by
Jake Ball
Jake Ball

Reputation: 808

MySQL Group By and Order By used together

This is a really strange one, I have been trying to solve this for quite some time now. I have searched high and low for an answer on this one, and I just simply can't find one. I am a but stuck, and would really appreciate it if someone could assist me here.

First and foremost, I have created an SQL Fiddle for this. It is simply so hard for me to explain this, and I felt that was the best way for people to get an idea of my schema and data: http://www.sqlfiddle.com/#!9/f5da89/3

So, here is what I am trying to do:

What's actually happening, is it's selecting the maximum date_recorded for each row (which is what I want, as there can be hundreds of these returned per query), it then returns what seems to be the correct data - however, upon closer inspection (using the index keys) you will see it is infact mixing up the contents completely. (id 334 has a different date_recorded to that of which was returned)

Am I misusing the GROUP BY functionality here? Can anyone point out exactly what I have done wrong here?

Thanks

Upvotes: 1

Views: 61

Answers (2)

Reto
Reto

Reputation: 1343

This one returns what you're expecting, isn't it?

SELECT p.date_recorded, p.id, p.userid, p.status, w.user_nicename, w.user_login, w.display_name
FROM wp_bbpas AS p
JOIN wp_users AS w ON w.id = p.userid
WHERE date_recorded >= NOW() - INTERVAL 24 HOUR
GROUP BY p.userId
ORDER BY date_recorded DESC

(You don't need the max(date_recorded) as the data is ordered by this filed)

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133380

you should use a subselect like this 

SELECT p.date_recorded as date_recorded, p.id, p.userid, p.status, w.user_nicename, w.user_login, w.display_name
FROM wp_bbpas AS p
JOIN wp_users AS w ON w.id = p.userid
WHERE date_recorded >= NOW() - INTERVAL 24 HOUR
and (p.userid, p.date_recorded) in (  select  userid, MAX(date_recorded)
                         from wp_bbpas  group by userid)
ORDER BY date_recorded DESC

Upvotes: 2

Related Questions