Find the answer to your Linux question:
Results 1 to 5 of 5
I am trying to list the last X unique topics. Here are the tables/columns in question: posts_t.topic_id, posts_t.post_time topics_t.topic_id, topics_t.topic_title Basically, I need to grab the last X unique topic_id ...
Enjoy an ad free experience by logging in. Not a member yet? Register.
  1. #1
    Banned
    Join Date
    Dec 2002
    Location
    Texas
    Posts
    242

    Help w/ phpBB Query


    I am trying to list the last X unique topics.
    Here are the tables/columns in question:

    posts_t.topic_id, posts_t.post_time
    topics_t.topic_id, topics_t.topic_title

    Basically, I need to grab the last X unique
    topic_id values based on their post_time.
    But, I need to pull the topic_title from the
    other table where both topic_ids are equal.

    Any help here?

    PS- I am not going to use any "MOD" utils.
    I want to do this with straight, simple SQL.
    (Keeps my upgrade paths simpler/easier.)

  2. #2
    Linux Engineer
    Join Date
    Jan 2003
    Location
    Lebanon, pa
    Posts
    994
    I am kinda confused as to exactly what you want. You want to select certian posts_t.topic_id based on posts_t.post_time and then select the topics_t.topic_title that matches tposts_t.topic_id==topics_t.topic_id?

  3. #3
    Linux Engineer
    Join Date
    Jan 2003
    Location
    Lebanon, pa
    Posts
    994
    Also what db server do you have?

  4. $spacer_open
    $spacer_close
  5. #4
    Banned
    Join Date
    Dec 2002
    Location
    Texas
    Posts
    242
    I am running phpBB with MySQL on the backend.

    I want the posts_t.topic_id based on posts_t.post_time
    (but only unique topic_id values). And then I need to
    pull the topics_t.topic_title for each of the topic_id #s.

    Using SELECT DISTINCT posts_t.topic_id, posts_t.post_time
    it gets the latest topic_ids, but they are not "unique" since it
    uses the entire row (and each post_time is unique).

  6. #5
    Linux Guru
    Join Date
    Oct 2001
    Location
    Täby, Sweden
    Posts
    7,578
    So what you're trying to do is to see which topics have been posted to lately, right?
    Now, it has been several months since I did anything SQL (or database, at all) related, but shouldn't you do something like this?
    Code:
    SELECT TOP 10 topics_t.topic_id, topics_t.topic_title, MAX(posts_t.post_time) AS post_time FROM posts_t RIGHT JOIN topics_t ON (posts_t.topic_id = topics_t.topic_id) GROUP BY topics_t.topic_id, topics_t.topic_title ORDER BY post_time DESC;
    I had to remember SQL while writing this, so it is by no means correct syntax. I just think it's the basic thing to do.

Posting Permissions

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