Select only one from each "group"

Question

If I run the following MySQL command :

SELECT * FROM mail f where f.toUserId = 15 AND f.toUserDeleted=0 ORDER BY f.sentDate DESC limit 0,100000

I will get all messages in correct order, I can clearly see the last messages on top that was sent last.

I do however need to destict this search something like this :

SELECT 
   f.id, 
   f.fromUserId, 
   f.fromUserNickName, 
   f.readDate, 
   f.sentDate, 
   f.subject, 
   f.fromUserDeleted, 
   f.toUserId, 
   f.toUserDeleted, 
   (SELECT count(*) FROM mail m1 WHERE m1.fromUserId=f.fromUserId AND m1.toUserId=f.toUserId) as messageCount 
FROM mail f 
WHERE 
   f.toUserId = 15 AND 
   f.toUserDeleted=0 
GROUP BY(f.fromUserId) 
ORDER BY 
   f.sentDate 
DESC 
   limit 0,100000

The problem is that this command will miss some users that was shown in the prev MySQL command?

EDit 1 :

I have tried this :

SELECT m1.* 
FROM mail m1 
LEFT JOIN mail m2 ON(m1.fromUserId = m2.fromUserId AND m1.sentDate < m1.sentDate) 
WHERE m2.id IS NULL 
ORDER BY m1.sentDate;

It takes forever, dont know if it is really returning anything.

Edit 2 :

Second try :

SELECT p1.id, p1.fromUserId, p1.fromUserNickName, p1.sentDate
FROM 
    mail p1 
INNER JOIN 
    (SELECT pi.id, MAX(pi.sentDate) as latestDate FROM mail pi GROUP BY pi.fromUserId) p2
    ON(p1.id = p2.id) 
WHERE 
    p1.toUserId = 15 
ORDER BY 
    p1.sentDate desc;

This do not return all users that have been sending mail to the user 15.

Edit 3:

Barmar´s second suggestions seemse to work :

SELECT m1.*, (SELECT count(*) FROM mail m3 WHERE m3.fromUserId=m1.fromUserId AND m3.toUserId=m1.toUserId) as messageCount 
FROM mail m1 
LEFT JOIN mail m2 ON (m1.fromUserId = m2.fromUserId 
                      AND m1.sentDate < m2.sentDate 
                      AND m2.toUserId = 15) 
WHERE m2.id IS NULL 
AND m1.toUserId = 15
ORDER BY m1.sentDate DESC;

This should get one message for each user, unless you have more than 100,000 users. — Barmar, Sep 12 '14 at 22:42
I suspect this is your actual question: http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group — Barmar, Sep 12 '14 at 22:44
@Barmar Yes, i now see that you are correct. The problem is that it is not selecting the newest message from each user and this creates the strange sort order. How can this be solved? — Banshee, Sep 12 '14 at 22:47
Make sure you have a composite index on `(fromUserId, sendDate)`, that should speed it up. — Barmar, Sep 12 '14 at 23:09
No, I tried the first solution in that post and there was no group. The group question seems to be vary slow? Maybe this is simpler to solve in a storedprocedure with two questions? — Banshee, Sep 12 '14 at 23:10
Do you have an index on `fromUserId`? That's critical to the performance. — Barmar, Sep 12 '14 at 23:12
No, I only have index for the id? Would a SP be faster with a cursor? — Banshee, Sep 12 '14 at 23:16
Unless you have an index on the field you're searching for, all methods will be slow. So just add the index and you'll be fine. — Barmar, Sep 12 '14 at 23:20
Okay, I added the index and the query is fast now but it is still not returning correct data? It looks like ti returns more or less every individual message. — Banshee, Sep 12 '14 at 23:21
`m1.sentDate < m1.sentDate` should be `m1.sentDate < m2.sentDate`. — Barmar, Sep 12 '14 at 23:26
Updated with a Edit 2. This time I try to get it like your link but I am for sure not getting all users that have been sending mail to me? — Banshee, Sep 12 '14 at 23:37

score 0 · Accepted Answer · answered Sep 12 '14 at 23:41

In both case, you're not filtering the table you join with down to just messages sent to your ID.

First method:

SELECT m1.* 
FROM mail m1 
LEFT JOIN mail m2 ON (m1.fromUserId = m2.fromUserId 
                      AND m1.sentDate < m2.sentDate 
                      AND m2.toUserId = 15) 
WHERE m2.id IS NULL 
AND m1.toUserId = 15
ORDER BY m1.sentDate DESC;

Second method:

SELECT p1.id, p1.fromUserId, p1.fromUserNickName, p1.sentDate
FROM 
    mail p1 
INNER JOIN 
    (SELECT pi.id, MAX(pi.sentDate) as latestDate 
     FROM mail pi 
     WHERE pi.toUserId = 15
     GROUP BY pi.fromUserId) p2
    ON (p1.id = p2.id) 
WHERE 
    p1.toUserId = 15 
ORDER BY 
    p1.sentDate desc;

Thanks, the first one does not work but the second one work just fine. — Banshee, Sep 12 '14 at 23:53

Select only one from each "group"

1 Answers1