How can I have an ORDER BY with LEFT JOIN?

Databases
1

My tables and their fields:
1.) client (fields: clientID, clientName)
2.) conversation (fields: conversationClientID, conversationText, conversationTimestamp)

My query:
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?

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 –

SELECT client.clientID
     , client.clientName 
     , conversation.conversationTimestamp
     , conversation.conversationText
  FROM client 
LEFT OUTER
  JOIN ( SELECT conversationClientID
              , MAX(conversationTimestamp) AS latest
           FROM conversation
         GROUP
             BY conversationClientID ) AS conv
    ON conv.conversationClientID = client.clientID
LEFT OUTER
  JOIN conversation
    ON conversation.conversationClientID = conv.conversationClientID
   AND conversation.conversationTimestamp = conv.latest
 WHERE client.clientType = 'prospect' 
ORDER 
    BY clientName

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

3

Cool, works perfect and thanks for the “redundancy” tip!