SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Thread: Latest Row Join

  1. #1
    SitePoint Evangelist sysice's Avatar
    Join Date
    Oct 2004
    Location
    Hackettstown, NJ
    Posts
    418
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Latest Row Join

    I'm working on a query that grabs a list of all the users plus the latest note for that user. The tables look something like this:

    user: id | email
    note: id | user | body | posted

    So I try something like this, but the body and the posted date don't match

    SELECT user.id, user.email, note.body, MAX(note.posted) FROM user JOIN note ON note.user = user.id GROUP BY user.id ORDER BY posted DESC LIMIT 20
    Any ideas on how to get this to work would be appreciated.

  2. #2
    SitePoint Evangelist sysice's Avatar
    Join Date
    Oct 2004
    Location
    Hackettstown, NJ
    Posts
    418
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I found a solution that works, though it isn't pretty.

    SELECT user.id, user.email, note.body, note.posted FROM user
    JOIN note ON note.user = user.id
    WHERE note.id IN (SELECT max(note.id) FROM note WHERE note.user = user.id ORDER BY note.posted DESC)
    GROUP BY user.id
    order by note.posted desc
    LIMIT 20

  3. #3
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    695
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Some unnecessary stuff there

    Code:
      SELECT user.id, user.email, note.body, note.posted 
       FROM user JOIN note ON note.user = user.id
     WHERE note.posted IN 
       (SELECT max(note.posted) 
          FROM note 
         WHERE note.user = user.id)
    order by note.posted desc
    LIMIT 20

  4. #4
    SitePoint Evangelist sysice's Avatar
    Join Date
    Oct 2004
    Location
    Hackettstown, NJ
    Posts
    418
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by swampBoogie
    Some unnecessary stuff there
    Thanks for the tips. They were leftover from other experiments.


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
  •