#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2002
    Posts
    99
    Rep Power
    0

    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)

    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. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,448
    Rep Power
    1751
    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).

    Not tested:
    Code:
    SELECT clientID
         , clientName
         , MAX(conversationTimestamp)
      FROM client
         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
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,179
    Rep Power
    4279
    simon, the timestamp of the latest conversation is not enough, you'll want the text that goes along with it!!!

    solution was posted over here --
    http://www.sitepoint.com/forums/show...with-LEFT-JOIN
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2002
    Posts
    99
    Rep Power
    0

    I got it from another forum, this works perfect!


    Code:
    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
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,179
    Rep Power
    4279
    Originally Posted by brainMan
    I got it from another forum, this works perfect!
    thanks for noticing the post just before yours
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo