January 8th, 2013, 03:21 PM
How can I have an ORDER BY with LEFT JOIN?
My tables and their fields:
1.) client (fields: clientID, clientName)
2.) conversation (fields: conversationClientID, conversationText, conversationTimestamp)
SELECT * FROM client LEFT JOIN conversation ON (conversationClientID = clientID) WHERE clientType = 'prospect' GROUP BY clientID ORDER BY clientName, conversationTimestamp DESC
I want all my clients on a list on alphabetic order and with that client I only want the last call to be shown.
With my query, the list of clients is ok, but it shows the first conversation of the client and not the last according to the timestamp. How can I achieve this?
January 8th, 2013, 05:49 PM
I am going to assume clientType is a column in your client table
I believe your problem is down to the use of the GROUP BY as that 'collapses' the data down, as you will have seen, to one result row per GROUP column. ANy other column in the result row that is not part of an aggregate function will be, effectively, a random choice from the values associated with the GROUP column(s).
LEFT JOIN conversation
ON conversationClientID = clientID
WHERE clientType = 'prospect'
GROUP BY clientID
ORDER BY clientName
The moon on the one hand, the dawn on the other:
The moon is my sister, the dawn is my brother.
The moon on my left and the dawn on my right.
My brother, good morning: my sister, good night.
-- Hilaire Belloc
January 8th, 2013, 06:25 PM
January 9th, 2013, 03:55 AM
I got it from another forum, this works perfect!
JOIN ( SELECT conversationClientID
, MAX(conversationTimestamp) AS latest
BY conversationClientID ) AS conv
ON conv.conversationClientID = client.clientID
ON conversation.conversationClientID = conv.conversationClientID
AND conversation.conversationTimestamp = conv.latest
WHERE client.clientType = 'prospect'
January 9th, 2013, 05:22 AM
thanks for noticing the post just before yours
Originally Posted by brainMan