SUB QUERY HElp

phprock

HEre is my table structure

topic has many threads
Thread has many posts

When i want display all the threads in a topic i want to sort it by last post ( if there any reply of a thread. other wise sort it by thread DESC..)

The following query works but it duplicate all the thread id,
if i have thread it 1,2,3 it keeps repeating.. I wanted group them by thread_id but i can't use as I m using ORDER BY sorting by post_date .

Thanks


SELECT thread_id, thread_title, post_title, post_date FROM thread, post WHERE thread_topic = 1 AND thread_id = post_thread
 ORDER  BY post_date DESC

cahva

Why cant you use GROUP by?? As I see it the only way to solve the problem.

SELECT thread_id, thread_title, post_title, post_date FROM thread, post WHERE thread_topic = 1 AND thread_id = post_thread
 GROUP by thread_id ORDER  BY post_date DESC

phprock

Thank you for the reply...

NOw its sorting by first post of each thread not on the last post of each thread.

Let say

i have three topics

PHP
Mysql
Apache

PHP has 3 threads

thread 1 has 2 posts (Posted on 1pm and 2 pm)

Thread 2 has 1 post posted on 3 pm

Thread 3 has 2 posts posted on 11 Am AND 8 PM

so you can see that thread 3 should be at the top because its last post is 8 PM
And thread 2 should be in the second position and thread 1 should be in the last position..

Now what is doing is sorting accroing to the first post of each thread.

Thread 2 First Post at 3 PM
THreds 1 First post at 1 PM
Thread 3 First post at 11 AM