brain — 2013-01-08T16:41:48-05:00 — #1
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?
r937 — 2013-01-08T18:18:26-05:00 — #2
to answer the question posed in your thread title, it's easy, because the FROM clause (where the LEFT OUTER JOIN is) and the ORDER BY clause are completely unrelated (except insofar as you can't sort by columns that you didn't retrieve from the tables in the FROM clause)
to answer the question posed in the text of your post, you need a subquery to identify which of the conversations is the latest one for each client
check out the subquery here first, then see how it's used to bring in the associated data --
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'
just a comment on style -- you've "embedded" the table name into each of the column names in each table, and this needless redundancy results in verbose queries
brain — 2013-01-09T03:52:44-05:00 — #3
Cool, works perfect and thanks for the "redundancy" tip!