CODEWITHSUNDEEP
mysqlsql
pkelly
pkelly

Reputation: 203

GROUP BY id After ORDER BY time

This query returns all items where the difference between the timestamps is less than 180 seconds.

The problem is, after all this is done, I need to then limit the results to the one most recent entry per facebook_id.

I tried using GROUP BY facebook_id, but it doesn't work because if I GROUP BY facebook_id before ORDER BY 'time', it picks the older entry instead of the newer entry which is not what I want.

Is there any way to GROUP BY after ORDER BY?

SELECT facebook_id, 
TIMESTAMPDIFF(SECOND, `time`, '$mytime') AS `timediff`
FROM `table`
WHERE `facebook_id` != $fbid
HAVING `timediff` <= '180'
ORDER BY `time` DESC

Thanks for your help!

Note: I did try the suggested solutions to this question but had no success. GROUP BY after ORDER BY

Upvotes: 0

Views: 361

Answers (2)

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

You can use a self join by calculating maximum value of time column and join with 2 conditions one with facebook_id and second to match the time from table to the max_time of second query which will return recent entry against each facebook_id

SELECT t.*, 
TIMESTAMPDIFF(SECOND, `time`, '$mytime') AS `timediff`
FROM `table` t
JOIN (
SELECT facebook_id,MAX(`time`) max_time FROM `table` GROUP BY facebook_id
) t1
ON(t.facebook_id= t1.facebook_id AND t.`time` = t1.max_time)
WHERE t.`facebook_id` != $fbid
HAVING `timediff` <= '180'
ORDER BY t.`time` DESC

Upvotes: 2

Devarsh Desai
Devarsh Desai

Reputation: 6112

 SELECT fid, timediff
 FROM (
          SELECT facebook_id as fid, 
          TIMESTAMPDIFF(SECOND, `time`, '$mytime') AS timediff
          FROM `table`
          WHERE `facebook_id` != $fbid
          HAVING `timediff` <= '180'
          ORDER BY `time` DESC
         ) entries
 GROUP BY entries.fid

Please let me know if you have any questions!

Upvotes: 1

Related Questions