MySQL Select Query Help

Napster

I have this table in my database with the following columns: id, thread, message, dateline. A sample of what the data looks like is as follows:

1 1 foo time()
2 1 bar time()+1
3 2 foo time()-2
4 3 foo time()-1
5 2 bar time()+2

Basically I want to return the first message of each thread, ordering it by the last dateline of each thread. So what I would want the SQL query to return would look like

3 2 foo time() - 2
1 1 foo time()
4 3 time() - 1

Now so far I have for my query:

mysql_query("SELECT * FROM table GROUP BY thread ORDER BY dateline") or die(mysql_error());

Basically this returns these three rows but not in the order of the last reply time

How do I fix this? Please advise.

FijiSmithy

Although I'm new here I think your question is more for an SQL forum than PHP.

But whay you want to Search for is something like

SELECT a.* FROM table As a WHERE a.dateline IN (SELECT MAX(b.dateline) from table AS b WHERE a.thread = b.thread)

Napster

Thanks for the help though it didn't seem to help.

I tried this as well but it doesn't seem to help either.

SELECT a.* FROM table AS a GROUP BY a.thread ORDER BY a.dateline IN (SELECT MAX(b.dateline) FROM table AS b WHERE a.thread = b.thread) DESC

FijiSmithy

That's wierd that the SQL didn't work..
The only other thing I can think of is to SELECT * From table ORDER BY thread, dateline DESC

Then in your PHP as you loop through the rows only echo out the first of each new thread...