MySQL GROUP By和ORDER BY冲突

Question

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.

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.

Answer 1

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.