SitePoint Sponsor

User Tag List

Results 1 to 11 of 11

Thread: Simple query

  1. #1
    SitePoint Addict
    Join Date
    Sep 2008
    Posts
    341
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Simple query

    primary_id / from_user_id / to_user_id / time / message

    1 / 33 / 189 / 1334865983 / "Hello there"
    2 / 44 / 76 / 1334897600 / "Hi Dave"
    3 / 63 / 33 / 1334907887 / "Hello, message here"
    4 / 33 / 63 / 1334908903 / "Hi again"

    I have a simple table like above for storing messages sent between 2 users.

    I need to display one record for each 'conversation' that a user has, so in this example, user 33 has had 2 conversations:
    - conversation 1: one message sent by user 33 in record 1 to user 189 (no reply messages or other messages between the two users)
    - conversation 2: one message received by 33 in record 3 and one sent in record 4, both to user 63

    I want to display the most recent message and show it like a header page where they click and view all messages between the two users.

    I only need help on the query to retrieve the data from table above so I can display like below. I have everything else under control.

    I am thinking GROUP BY, but how do I return the latest message (and time field) and how do I GROUP BY considering the user id could be in either the from_user_id or to_user_id field?

    So I need to show the data like this in a header page (I just need help on the query that will sort the data to allow me to do this):

    ----

    User 33's messages:
    -------------------------

    Conversation number 1 (with user 189)
    (date = 1334865983) Hello there (shows latest message)

    Click HERE to View

    ---------------------------------

    Conversation number 2 (with user 63)
    (date = 1334908903) Hi again (shows latest message)

    Click HERE to View

  2. #2
    SitePoint Member
    Join Date
    Apr 2012
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What database are you using?
    Last edited by paul_wilkins; Apr 22, 2012 at 05:14.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    this is untested --
    Code:
    SELECT m.with_user
         , m.time
         , m.message
      FROM ( SELECT w.with_user
                  , MAX(w.time) AS max_time
               FROM ( SELECT CASE WHEN t.from_user_id = 33
                                  THEN t.to_user_id
                                  ELSE t.from_user_id
                              END AS with_user
                           , t.time
                        FROM messages AS t
                       WHERE 33 IN ( t.from_user_id , t.to_user_id ) ) AS w
             GROUP
                 BY w.with_user ) AS x
    INNER
      JOIN messages AS m
        ON m.time = x.max_time
       AND ( m.from_user_id = 33 and m.to_user_id = x.with_user
          OR m.from_user_id = x.with_user and m.to_user_id = 33 )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Member
    Join Date
    Apr 2012
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you are using SQL Server (2005 onwards) or Oracle you can look at the RANK or DENSE_RANK function partitioned by user ID and ordered by message datetime descending.

    The approach would be a selecting from a derrived table including a column of RANK where the rank column equals 1.

    I don't think MySQL supports a RANK function, which is unfortunate as you will probably have to go with the much less efficient join on I'd with a MAX function.


    Sent from my BlackBerry 9900 using Tapatalk

  5. #5
    SitePoint Addict
    Join Date
    Sep 2008
    Posts
    341
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks r937, but it has errors.

    It keeps saying w.with_user isn;t a valid field. I then messed around and it started saying that m.with_user isn't a valid field.

    Any ideas?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by johnsmith153 View Post
    Any ideas?
    please show your query, and the error message
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict
    Join Date
    Sep 2008
    Posts
    341
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the reply. This is what is displayed:
    --------
    Unknown column 'm.with_user' in 'field list'
    SELECT m.with_user , m.time , m.msg FROM ( SELECT w.with_user , MAX(w.time) AS max_time FROM ( SELECT CASE WHEN t.from_id = 22 THEN t.to_id ELSE t.from_id END AS with_user , t.time FROM messages_table AS t WHERE 22 IN ( t.from_id , t.to_id ) ) AS w GROUP BY w.with_user ) AS x INNER JOIN messages_table AS m ON m.time = x.max_time AND ( m.from_id = 22 and m.to_id = x.with_user OR m.from_id = x.with_user and m.to_id = 22 )
    ---------
    'with_user' isn't a field in my table, but I'm pretty sure that it isn't supposed to be based on the query you provided. The exact fields in my table are:
    - time
    - msg
    - to_id
    - from_id

  8. #8
    SitePoint Member
    Join Date
    Apr 2012
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try x.with_user ?
    Last edited by paul_wilkins; Apr 22, 2012 at 05:13.

  9. #9
    SitePoint Addict
    Join Date
    Sep 2008
    Posts
    341
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No that doesn't work either.

    Thanks for looking though.

  10. #10
    SitePoint Addict
    Join Date
    Sep 2008
    Posts
    341
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry, my mistake, changing to 'x.with_user' works perfectly.

    I had been messing around with other parts of the query and when I realised my error it worked straight away.

    Thanks again to both.

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by johnsmith153 View Post
    Sorry, my mistake, changing to 'x.with_user' works perfectly.
    well done

    problem is, this wasn't your error, it was mine!!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •