SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Addict
    Join Date
    Feb 2004
    Location
    Netherlands
    Posts
    381
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Get latest forum topics

    I want to get the latest posts from the `posts` table AND I want the correct topic row from another table. But I only want to get 1 latest post for every topic, and not all posts in that topic, how to do that?

    [posts]
    ID
    topicID
    name
    content
    timestamp

    [topics]
    ID
    categoryID
    sticky
    deleted
    highlighted

    posts.topicID = topics.ID


    So I want to get ONE latest posts per topicID where category = 4 ORDER BY `timestamp` DESC

    thanks!

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,501
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    To quote Rudy: what you want is commonly called the row having the groupwise max.
    Check out this thread: http://www.sitepoint.com/forums/mysq...es-763414.html

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    what you want is commonly called the row having the groupwise max

    see if this thread from last week gives you a hint -- http://www.sitepoint.com/forums/mysq...es-763414.html

    ignore the fact that there were three tables involved in that one, you have only two
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Addict
    Join Date
    Feb 2004
    Location
    Netherlands
    Posts
    381
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    lol, nice replies

    Any chance that's explained inside your book which lays next to me?

  5. #5
    SitePoint Addict
    Join Date
    Feb 2004
    Location
    Netherlands
    Posts
    381
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Aargh, I just can't figure it out, can you write me a query that suits my situation? There's too much going on in that other thread

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Sergeant View Post
    ...can you write me a query that suits my situation?
    i can, but i'd rather help you do it

    have a look at this other thread, also quite recent...

    http://www.sitepoint.com/forums/mysq...te-763979.html

    almost identical to your situation
    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
  •