how to select newest record from one table joined with another

[belteshazzar]

Hi, I hope someone can help me with a sql problem I'm having. I'm developing a simple bulletin board system. I have a table of posts and a table of threads which get joined on posts.thread=threads.id.

What i want to do is show a list of active threads with the newest posts in each. In other words i need to select 5 distinct threads using the posts table in the selection criteria. I've played around with distinct, group by, order by and max(posts.posted) and have come up with nothing.

I tried using the following but it doesn't return the newest post in each thread.

SELECT threads.id,posts.title,posts.posted FROM threads,posts WHERE threads.id=posts.thread GROUP BY threads.id ORDER BY posts.posted DESC;

any ideas?

[Brandoe85]

Whichever field is your field that holds the datetime of when the post was made, you can: order by dateField Desc limit 5.

[belteshazzar]

I've tried that, but it doesn't work. it will select the 5 newest posts but ignores if they are from the same thread.

I want to make sure the result only has a post from each thread.

[Brandoe85]

What are structures of your tables?

[belteshazzar]

I think what i need is an alteration of the following sql.

select thread,title,max(posted) from posts group by thread;

the title value is not the value of the record that max(posted) comes from! is there a way of making sure that it is?

[belteshazzar]

my tables are (simplified):

posts ( id int,thread int,title varchar,body text,posted datetime);
threads( id int, forum int, title varchar, body text, posts int );
forums ( id int, title varchar );

the tables are joined on:

posts.thread=threads.id
threads.forum=forums.id

[Kiwi]

I'm unsure what the problem is:

PHP Code:

SELECT 
 P.id,
 P.title as PostTitle, 
 MAX(P.posted)
 T.id,
 T.title as TheadTitle,
 F.id,
 F.title as ForumTitle
FROM
 posts P,
 threads T,
 forums F
WHERE P.thread = T.id
 AND T.forum = F.id
GROUP BY
 P.id,
 PostTitle,
 T.id,
 ThreadTitle,
 F.id,
 ForumTitle 


[belteshazzar]

that code gives a list of posts in order of date when they were posted with their respective threads and forums. however, i want to restrict that so that there is only one post (the newest post) from each thread in the result set.

I've found a partial solution on the mysql site:

SELECT article, dealer, price
FROM   shop s1
WHERE  price=(SELECT MAX(s2.price)
              FROM shop s2
              WHERE s1.article = s2.article);

unfortuantely, i'm using a version earlier than 4.1 that doesn't support the above so i have to use temporary tables (according to the mysql docs).

thanks for you help. i should be able to get a solution for what i want. the url, by the way, of the mysql docs that i found this at is: http://dev.mysql.com/doc/mysql/en/ex...group-row.html

[raf]

the problem with kiwi's code is the

GROUP BY
P.id,
PostTitle,
T.id,
ThreadTitle,
F.id,
ForumTitle

--> this wount cause any grouping at all. MySQL doesn't follow the standard implementation of GROUP BY and doesn't require that all fields are part of an agregarionfunction or the "group by"-clause. Kiwi's code is what you would expect for a standard SQL-version, but it wouldn't work because each post will become a seperate 'group'.

Your "group by" clause can only contain T.id

Try:

SELECT
MAX(P.posted) as PostDate,
P.id,
P.title as PostTitle,
T.id,
T.title as TheadTitle,
FROM
posts P,
threads T,
WHERE P.thread = T.id
GROUP BY
T.id


If that doesn't work, then post (a part) of a dumpfile of these 2 tables so we can try it out.

[belteshazzar]

Promising, but it doesn't work, the problem being that the date found by max(posts.posted) isn't from the posts.id row that is returned. It seems that in this case mysql returns the date of the newest post in each thread, but returns a random post id.

HANG ON! i dont really need the post id! so it works!

thanks for that!

[belteshazzar]

but ... is it possible to order it by max(posted) somehow ... so in mysql version 4.0 i'm stuck creating a temp table.

[raf]

Originally Posted by belteshazzar

but ... is it possible to order it by max(posted) somehow ... so in mysql version 4.0 i'm stuck creating a temp table.

SELECT
MAX(P.posted) as PostDate,
P.title as PostTitle,
T.id,
T.title as TheadTitle,
FROM
posts P,
threads T,
WHERE P.thread = T.id
GROUP BY T.id
ORDER BY PostDate DESC