SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid View

  1. #1
    SitePoint Addict brain's Avatar
    Join Date
    Sep 2001
    Location
    Amsterdam
    Posts
    249
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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 --
    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
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict brain's Avatar
    Join Date
    Sep 2001
    Location
    Amsterdam
    Posts
    249
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cool, works perfect and thanks for the "redundancy" tip!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •