Thread: Tricky one

    #1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2000
    Location
    Bremgarten, Switzerland
    Posts
    17
    Rep Power
    0
    ok,

    here's a tricky one: I am writing a forum.
    I save all the messages of one forum into one MySQL table.
    The important fields of every record are:

    id: unique id of message
    parent_id: the id of the parent message (it's a tree style forum)
    thread_id: the id of the thread

    Now I want to find out which thread is the most popular one. I can easily find the number of replies for a single thread, that would be
    SELECT * FROM msg_table WHERE thread_id=[thread id]
    and then substract 1 from the number of result rows.
    But if I would do this for every single thread, basically loop through all of them, save the number of result rows in an array, and then bubblesort it........ man that would really be a pain in the neck.

    Did i forget to put in a num_replies field or am I just too stupid to find a convenient SQL query to do this task for me?

    thanks for any ideas
    -Alex Suzuki
  2. #2
  3. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2000
    Posts
    8
    Rep Power
    0
    Not debugged, but try this out

    select
    thread_id,
    count(id)
    from
    msg_table
    group by
    thread_id
    order by
    thread_id;

  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2000
    Location
    Bremgarten, Switzerland
    Posts
    17
    Rep Power
    0
    Thanks,

    that works like a dream!

    -asuzuki
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2000
    Location
    Bremgarten, Switzerland
    Posts
    17
    Rep Power
    0
    I was wondering, the query outputs two columns and is ordered by thread_id. But how can I order it by count(id), so that the thread with the most messages appears on top...? Otherwise I'll have to write the whole thing in an array and bubblesort it.
    Sorry for the last message I was just euphoric because the output looked cool

    - Alex Suzuki
  8. #5
  9. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    You'll have to alias the count() and order by the alias.

    select
    thread_id,
    count(id) as cnt
    from
    msg_table
    group by
    thread_id
    order by
    cnt;
  10. #6
  11. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2000
    Posts
    8
    Rep Power
    0
    Yes, sorry. I said I didn't check it for err's.

    You got your help though


    Hubbard
  12. #7
  13. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2000
    Location
    Bremgarten, Switzerland
    Posts
    17
    Rep Power
    0
    Thanks a lot for your help. Does anybody know where I can find a good SQL-Tutorial on the web, so I don't have to post questions like that one ?

    -Alex Suzuki

Similar Threads

  1. .htaccess Help... tricky request!
    By spishack in forum Apache Development
    Replies: 0
    Last Post: November 19th, 2003, 12:50 PM
  2. tricky join question
    By artypants in forum MySQL Help
    Replies: 5
    Last Post: September 5th, 2003, 06:19 PM
  3. Need help with tricky SQL query
    By John Cook in forum MySQL Help
    Replies: 8
    Last Post: September 2nd, 2003, 02:09 PM
  4. Very tricky mysql query
    By Franklyn in forum MySQL Help
    Replies: 6
    Last Post: September 21st, 2001, 08:32 PM
  5. Tricky tricky tricky... a exercise in SQL JOIN
    By joetek in forum MySQL Help
    Replies: 1
    Last Post: September 18th, 2001, 08:02 AM

IMN logo majestic logo threadwatch logo seochat tools logo