drex88669
2013-06-28 23:50 阅读 136
已采纳

MySQL GROUP By和ORDER BY冲突

So i'm trying to select some contacts (TABLE2) based on their HISTORY (TABLE1). So I am testing with this SQL Query:

SELECT
    contacts_history.userId,
    contacts_history.contactId,
    contacts_history.dateAdded,
    contacts.firstName
FROM
    contacts_history
INNER JOIN contacts ON contacts_history.contactId = contacts.contactId
AND contacts_history.userId = contacts.userId
GROUP BY
    contacts.contactId
ORDER BY
    contacts_history.dateAdded DESC
LIMIT 0, 10

But i'm noticing that i'm not getting the most 'recent' values. If I remove the GROUP BY I get totally different DATE RANGES.

I have tried using DISTINCT, but no decent response either.

Take a look at the comparison table results.

enter image description hereenter image description here

I'm having a hard time getting the most recent items, without having duplicate contactId's in there.

Any help would be greatly appreciated... i'm sure this is super basic, just not sure why it's not working the best.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

1条回答 默认 最新

  • 已采纳
    duafagm1066 duafagm1066 2013-06-28 23:57

    GROUP BY is for use with aggregate methods. Without something like MAX(contacts_history.dateAdded) it doesn't make any sense to use GROUP BY

    I think what you want is along the lines:

    SELECT
        contacts_history.userId,
        contacts_history.contactId,
        MAX(contacts_history.dateAdded) AS last_date,
        contacts.firstName
    FROM
        contacts_history
    INNER JOIN contacts ON contacts_history.contactId = contacts.contactId
    AND contacts_history.userId = contacts.userId
    GROUP BY
        contacts_history.userId, 
        contacts.contactId
        contacts.firstName
    ORDER BY
        last_date DESC
    LIMIT 0, 10
    

    This should give you (I haven't tested it) one line for each user and contact, sorted by the date the contact was added.

    点赞 评论 复制链接分享

相关推荐