Use limit before group by

Question

I am using the following query to get the latest 2 messages of the same conversation:

SELECT *
FROM messages
WHERE conversation_id
IN ( 122806, 122807 )
GROUP BY conversation_id
ORDER BY sent_on DESC
LIMIT 2

enter image description here

Which return message7 and message3 as result. What I need is to get latest 2 messages grouped by conversation_id, therefore the result should be:

message3
message1
message4
message5

The best way to easily use GROUP BY with ORDER BY are subqueries. — aurbano, Mar 28 '14 at 11:11
possible duplicate of [Get top n records for each group of grouped results](http://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results) — Rikesh, Mar 28 '14 at 11:12
@Rikesh, I have a table with above 100,000 records, so I can't make similar query — Moussawi7, Mar 28 '14 at 11:14

Gordon Linoff · Accepted Answer · 2014-03-28T11:42:12.473

1

The canonical way to do this is with a counter in the where clause:

select m.*
from message m
where 2 >= (select count(*)
            from message m2
            where m2.conversation_id = m.conversation_id and
                  m2.sent_on >= m.sent_on
           );

An index on message(conversation_id, sent_on) would definitely help this query. This also assumes that sent_on is unique. Otherwise, you can just use id.

A more efficient method is to use variables:

select m.*
from (select m.*,
             @rn := if(@conversation_id = conversation_id, @rn + 1, 1) as rn,
             @conversation_id := conversation_id
      from message m cross join
           (select @conversation_id := '', @rn := 0) const
      order by conversation_id, sent_on desc
     ) m
where rn <= 2;

edited Mar 28 '14 at 11:42

answered Mar 28 '14 at 11:26

Gordon Linoff

1,242,037
58
646
786

I tried the first method, which crashed my mysql server :'(, let me try the second. (I have more that 200,000 records) – Moussawi7 Mar 28 '14 at 11:29
The second method returned :Unknown column 'message_id' – Moussawi7 Mar 28 '14 at 11:30
Also the second method was crashed – Moussawi7 Mar 28 '14 at 11:37
When i make some limitation it worked fine: and conversation_id in (122806,122807) – Moussawi7 Mar 28 '14 at 11:45

score 1 · Answer 2 · answered Mar 28 '14 at 11:30

One way to go about this using GROUP_CONCAT() and the SUBSTRING_INDEX() but it will show you the messages seperated by seperator you specified in the query with conversation id not as individual row foreach message,you can use ORDER BY clause in group_concat function also i have ORDER BY sent_on DESC so the messages will be grouped and ordered by sent_on

SELECT conversation_id,
SUBSTRING_INDEX(
GROUP_CONCAT(message ORDER BY sent_on DESC SEPARATOR '||'),
'||',2) two_messages
FROM messages
/*
optional where filter i have provided example for all conversations
 WHERE conversation_id 
IN ( 122806, 122807 ) */
GROUP BY conversation_id
ORDER BY sent_on DESC

Also note there is a default 1024 character limit set on GROUP_CONCAT() but you can also increase it by following the GROUP_CONCAT() manual

Thanks, It worked fine, but as you mentioned it show the message seperated by seperator, so you think that there are no way to achieve it by row?! — Moussawi7, Mar 28 '14 at 11:35
I guess there should be a way to go about getting the messages in each row but i can't have a solution for this :) — M Khalid Junaid, Mar 28 '14 at 11:37

score 0 · Answer 3 · answered Mar 28 '14 at 11:23

0

Try GROUP BY with ORDER BY something like this:

SELECT GROUP_CONCAT(messages)
FROM(
    SELECT *
    FROM messages
    ORDER BY sent_on DESC
    )temp
GROUP BY conversation_id
LIMIT 2;

answered Mar 28 '14 at 11:23

avisheks

1,178
10
27

It will, if you want all the messages try group_concatenating the messages, edited the ans – avisheks Mar 28 '14 at 11:28

Use limit before group by

3 Answers3