Take the 2-minute tour ×

I have searched all over and can't manage to get this to work. I am trying to build a question that groups my records within 5 minutes - and displays the last entry. However I managed to get there halfway, but my query is only returning the first entry (XX=10).

I do have the following data:

ID        XX       DATETIME
N1         5       2009-05-01 13:00:00
N1        10       2009-05-01 13:04:49
N2         7       2009-05-02 14:00:00

and I want my group by to give me this:

N1      10
N2       7

I have uses this group by statment:

((60/15) * HOUR(created_on) + FLOOR(MINUTE(created_on) / 15)) 

Any one out there that has any ideas on how this query should look like?

Best regards, Joakim

share|improve this question
Doing this for 10 minutes is mildly trivial, but it's a highly expensive task... why do you need to group like this ? –  Khez Apr 9 '11 at 15:09
I dont follow you? Can u explain further what you mean? –  Joakim Krassman Apr 9 '11 at 18:18
You can do GROUP BY SUBSTR(datetime,15);. It will give you sets of 10 minutes. –  Khez Apr 9 '11 at 18:30
Hmmm, that doesnt seems to do what I am trying to achive? I want to group a recordset and get me the latest entry. –  Joakim Krassman Apr 9 '11 at 18:41
sigh... SELECT *, SUBSTR( datetime, 1, 16 ) as foo FROM tbl GROUP BY foo ORDER BY foo DESC LIMIT 1; Highly unoptimized way of doing anything. –  Khez Apr 9 '11 at 19:10
add comment

2 Answers

You can simply use: ((60/12) * HOUR(created_on) + FLOOR(MINUTE(created_on) / 5))

share|improve this answer
add comment
UNIX_TIMESTAMP(created_on) - UNIX_TIMESTAMP(created_on) % 5
share|improve this answer
oh! this seems to be a stale question! –  newtover Dec 26 '11 at 14:38
add comment

Your Answer


