Need help with select by time

[nakins]

Hello,
I'm trying to make a php script that uses a free php forum for content management. I have made a script to select the newest topics that are posted to certain forums. For example, say I have a category Sports and a forum called Football. And say I have a post (each) for the superbowl, the rose bowl and the orange bowl and the are replies to each of those topics. I want to select the originating post for the superbowl rose and orange, since the are the first posts for each topic.
I have tried:
"SELECT topic_id FROM posts WHERE forum_id = '$ke' ORDER BY post_time DESC ";

In this case, $ke would be Football. But this doesn't work like I wanted. As it selects the first unique topic_id it comes to and once it has a group of unique topic-id's, orders them by post_time. I think this problems come from an reply entry being made into the table before the original entry. This happens, I think, from posts being deleted and leaving an open spot in the table.

So, I would like to select not only a unique topic_id, but the earlist topic_id.

I've tried: SELECT topic_id, min(post_time)....but that just gives me one id total for each category. I've tried "SELECT topic_id FROM posts WHERE forum_id = '$ke' AND post_time = min(post_time) ORDER BY post_time DESC "; but this gives me an error (invalid use of group by).

Can anyone help me out here?
Thanks

[raf]

Welcome here !

I don't ealy understand your explanation, but i think you want the id of baoth original post and last post of a thread. Right?

Then you need something like

(SELECT topic_id FROM posts WHERE forum_id = '$ke' ORDER BY post_time ASC LIMIT 1) UNION (SELECT topic_id FROM posts WHERE forum_id = '$ke' ORDER BY post_time DESC LIMIT 1)

[nakins]

Here is an example table to show what I'm talking about.
With the select statement:
SELECT post_id FROM posts WHERE topic_id = '$var' ORDER BY post_time DESC"

POST_ID | TOPIC _ID | FORUM_ID | POST_TIME
1 1 1 1000
2 1 1 1100
7 2 1 2000
6 3 1 2500
5 2 1 1500

POST_ID 1 is selected as it is the first record
with a TOPIC_ID of 1.
POST_ID 7 is selected as it is the first record
with a TOPIC_ID of 2.
POST_ID 6 is selected as it is the first record
with a TOPIC_ID of 3.

These are then sorted by the POST_TIME entry
with the newest one first.

The result being:
6
7
1

The problem with this is that POST_ID 7 is not the original post,
it is a reply to the original post. The original post being POST_ID 5.
This is because the POST_ID 5 is not in desc order, and comes after 7.
This happens as a result of a row/post being deleted form the table and then
a new post entry being entered into the table in the vacant row.
I did not set this table up. It is a part a free, open source message board.

Anyway, I would like to write a select statement the selects the post_id
based on the topic_id and then the oldest post_time for that topic_id.

Example, if there were four post_id's with a topic_id of 1, the select statement would return the oldest of those four, and not the first post_id it comes across with a topic_id of 1.

Any suggestions?
Thanks :thumbsup:

[raf]

You're not making any sense at all.

Since you have this topic_id = '$var' inside your where-clause, you will always just have posts from one topic_id. So everything of your example is simply nonsense.

If you need the first and the last post for a topic, then the query i posted will deliver exactly that. It selects the records from that topic and then sorts them by post_time. Once in ascending order, and only retrievig the first record --> the oldest post. Once sorted descending and again only the first record --> the latest post.

This then returns a recordset with two records.In your exmple for topic 2, this would be 5 and 7. To make the recordsetprocessing easier, you could add an extra column like

(SELECT topic_id,'initial' FROM posts WHERE forum_id = '$ke' ORDER BY post_time ASC LIMIT 1) UNION (SELECT topic_id, 'last' FROM posts WHERE forum_id = '$ke' ORDER BY post_time DESC LIMIT 1)

which add's an extra column, with name 'initial' and possible values 'initial' or 'last