MySQL: grouping question

Question

I have a 'stream_update' table that stores the updates users post to the groups they're subscribed to. The table has the following fields:

id (index, auto increment)
user_id
group_id
stream_update
timestamp

Basically I'm trying to write a query that gets the last stream update for every user in each group they're subscribed to. So if a user is subscribed to 3 groups, it would get the last update he has written in each of these groups.

I've tried the following:

SELECT * FROM stream_update GROUP BY group_id, user_id

This seemed to work, but it would get the oldest records not the newest. Adding "ORDER BY" to that doesn't work, since it orders the already-fetched records only.

Any idea how I can write a query for this?

Your help is much appreciated :)

Joe Stefanelli · Accepted Answer · 2010-11-17 20:28:35Z

3

select su.user_id, su.group_id, su.stream_update, su.timestamp
    from stream_update su
        inner join (select user_id, group_id, max(timestamp) as maxtime
                        from stream_update
                        group by user_id, group_id) m
            on su.user_id = m.user_id
                and su.group_id = m.group_id
                and su.timestamp = m.maxtime

answered Nov 17, 2010 at 20:28

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jt. · Accepted Answer · 2010-11-17 20:28:59Z

0

Have you tried something like this:

SELECT id,user_id,group_id,max(stream_update),timestamp
FROM stream_update 
GROUP BY group_id, user_id

answered Nov 17, 2010 at 20:28

jt.

7,7354 gold badges29 silver badges24 bronze badges

Comments

Tim · Accepted Answer · 2010-11-17 20:35:05Z

0

Does this work?

 select T1.user_id, t1.group_id, t2.stream_update
 from
  (     
  select  user_id, group_id, max(timestamp) as latesttimestamp
  from streamtable
  group by user_id, group_id
  )  as T1

  inner join  streamtable as T2
  on T1.user_id = T2.user_id
  and T1.group_id = T2.group_id
  and T1.latesttimestamp = T2.timestamp

answered Nov 17, 2010 at 20:35

Tim

8,99031 gold badges111 silver badges197 bronze badges

Comments

Shadow Radiance · Accepted Answer · 2010-11-17 21:03:33Z

0

Possibly select from a select... so do your regular select (above) and include the timestamp in the output

then wrap it in a

SELECT whatever-fields you want FROM (
  inner-select-statement with grouping
)
ORDER BY timestamp

Something like that should give you what I think you are after.

answered Nov 17, 2010 at 21:03

Shadow Radiance

1,35913 silver badges21 bronze badges

Collectives™ on Stack Overflow

MySQL: grouping question

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related