drex88669
2013-06-28 23:50
浏览 168
已采纳

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.

图片转代码服务由CSDN问答提供 功能建议

所以我试图根据他们的HISTORY(TABLE1)选择一些联系人(TABLE2)。 我是 使用此SQL查询进行测试:

  SELECT 
 contacts_history.userId,
 contacts_history.contactId,
 contacts_history.dateAdded,
 contacts.firstName 
FROM 
 contacts_history \  nINNER JOIN联系人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   
 
 

但我注意到我没有得到最“最近”的价值观。 如果我删除GROUP BY,我会得到完全不同的DATE RANGES。

我尝试过使用DISTINCT,但也没有得到正确的响应。

拿一个 查看比较表结果。

我很难获得 最近的项目,没有重复的contactId在那里。

任何帮助都将不胜感激......我确信这是超级基础,只是不确定为什么它不是最好的。

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • 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.

    已采纳该答案
    打赏 评论

相关推荐 更多相似问题