CODEWITHSUNDEEP
phpmysqlgroup-bydistinct
Justin
Justin

Reputation: 2530

MySQL GROUP By and ORDER BY conflict

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 here enter 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.

Upvotes: 0

Views: 680

Answers (1)

Sean Redmond
Sean Redmond

Reputation: 4114

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.

Upvotes: 1

Related Questions