Select, count(), group by and order by

Here is one that has had me puzzled for a day or so. I've built a simple
online forum on a club web site (not accessible by the public) using
MySQL/PHP, and I cannot make it sort correctly. I have found a lot of
postings and web pages about the topic, but none of the solutions have
worked.

Two simple tables in a 1-M:

tblforumtopic
top_pk INTEGER primary key AUTO-INCREMENT
csubject VARCHAR(60) the topic heading
dpost DATETIME when posted

tblforumpost
pos_pk INTEGER primary key AUTO-INCREMENT
top_fk INTEGER foreign key
mbody TEXT text of the post
dpost DATETIME when posted
... and a couple of others not relevant here

I want to display the Topics, with the subject line, the number of posts
in the thread, and the datetime of the *last* post. Here is what I've
been working with, plus many variations based on advice seen 'round,
none of which worked.

SELECT tblforumtopic.top_pk, tblforumtopic.csubject,
tblforumpost.dpost, COUNT(tblforumpost.pos_pk) AS ncnt
FROM tblforumtopic, tblforumpost
WHERE tblforumpost.top_fk = tblforumtopic.top_pk
GROUP BY tblforumtopic.top_pk
ORDER BY tblforumpost.dpost DESC

...which always returns the datetime of the *first* post and not the
last.

The web server is running MySQL server version: 4.1.21-standard. Any
ideas, please?


--
-bts
-Motorcycles defy gravity; cars just suck

Beauregard T. Shagnasty wrote:

> Here is one that has had me puzzled for a day or so. I've built a simple
> online forum on a club web site (not accessible by the public) using
> MySQL/PHP, and I cannot make it sort correctly. I have found a lot of
> postings and web pages about the topic, but none of the solutions have
> worked.
>
> Two simple tables in a 1-M:
>
> tblforumtopic
> top_pk INTEGER primary key AUTO-INCREMENT
> csubject VARCHAR(60) the topic heading
> dpost DATETIME when posted
>
> tblforumpost
> pos_pk INTEGER primary key AUTO-INCREMENT
> top_fk INTEGER foreign key
> mbody TEXT text of the post
> dpost DATETIME when posted
> .. and a couple of others not relevant here
>
> I want to display the Topics, with the subject line, the number of posts
> in the thread, and the datetime of the *last* post. Here is what I've
> been working with, plus many variations based on advice seen 'round,
> none of which worked.
>
> SELECT tblforumtopic.top_pk, tblforumtopic.csubject,
> tblforumpost.dpost, COUNT(tblforumpost.pos_pk) AS ncnt
> FROM tblforumtopic, tblforumpost
> WHERE tblforumpost.top_fk = tblforumtopic.top_pk
> GROUP BY tblforumtopic.top_pk
> ORDER BY tblforumpost.dpost DESC
>
> ..which always returns the datetime of the *first* post and not the
> last.
>
> The web server is running MySQL server version: 4.1.21-standard. Any
> ideas, please?
>
>
> --
> -bts
> -Motorcycles defy gravity; cars just suck
I assume that the *last* post can be identified by either it having the
highest pos_pk value or the most recent dpost datetime for a given
topic. In which case this is yet another time when the "Strawberry
Query" is what is required. Either one of the following queries should
work to get you the datetime of the latest post. You may need a second
query to get the count of posts as well, I'll need to play with it a
bit to see if it is possible in one JOIN query but if not you can
always perform an extra JOIN to a subquery to perform the count:

SELECT t.top_pk, t.csubject,
p1.dpost
FROM tblforumtopic t
JOIN tblforumpost p1 ON t.top_pk = p1.top_fk
LEFT JOIN tblforumpost p2 ON p1.top_fk = p2.top_fk AND p2.pos_pk >
p1.pos_pk
WHERE p2.pos_pk IS NULL

or using the datetime

SELECT t.top_pk, t.csubject,
p1.dpost
FROM tblforumtopic t
JOIN tblforumpost p1 ON t.top_pk = p1.top_fk
LEFT JOIN tblforumpost p2 ON p1.top_fk = p2.top_fk AND p2.dpost >
p1.dpost
WHERE p2.dpost IS NULL

Captain Paralytic wrote:

> Beauregard T. Shagnasty wrote:
>
> > Here is one that has had me puzzled for a day or so. I've built a simple
> > online forum on a club web site (not accessible by the public) using
> > MySQL/PHP, and I cannot make it sort correctly. I have found a lot of
> > postings and web pages about the topic, but none of the solutions have
> > worked.
> >
> > Two simple tables in a 1-M:
> >
> > tblforumtopic
> > top_pk INTEGER primary key AUTO-INCREMENT
> > csubject VARCHAR(60) the topic heading
> > dpost DATETIME when posted
> >
> > tblforumpost
> > pos_pk INTEGER primary key AUTO-INCREMENT
> > top_fk INTEGER foreign key
> > mbody TEXT text of the post
> > dpost DATETIME when posted
> > .. and a couple of others not relevant here
> >
> > I want to display the Topics, with the subject line, the number of posts
> > in the thread, and the datetime of the *last* post. Here is what I've
> > been working with, plus many variations based on advice seen 'round,
> > none of which worked.
> >
> > SELECT tblforumtopic.top_pk, tblforumtopic.csubject,
> > tblforumpost.dpost, COUNT(tblforumpost.pos_pk) AS ncnt
> > FROM tblforumtopic, tblforumpost
> > WHERE tblforumpost.top_fk = tblforumtopic.top_pk
> > GROUP BY tblforumtopic.top_pk
> > ORDER BY tblforumpost.dpost DESC
> >
> > ..which always returns the datetime of the *first* post and not the
> > last.
> >
> > The web server is running MySQL server version: 4.1.21-standard. Any
> > ideas, please?
> >
> >
> > --
> > -bts
> > -Motorcycles defy gravity; cars just suck
> I assume that the *last* post can be identified by either it having the
> highest pos_pk value or the most recent dpost datetime for a given
> topic. In which case this is yet another time when the "Strawberry
> Query" is what is required. Either one of the following queries should
> work to get you the datetime of the latest post. You may need a second
> query to get the count of posts as well, I'll need to play with it a
> bit to see if it is possible in one JOIN query but if not you can
> always perform an extra JOIN to a subquery to perform the count:
>
> SELECT t.top_pk, t.csubject,
> p1.dpost
> FROM tblforumtopic t
> JOIN tblforumpost p1 ON t.top_pk = p1.top_fk
> LEFT JOIN tblforumpost p2 ON p1.top_fk = p2.top_fk AND p2.pos_pk >
> p1.pos_pk
> WHERE p2.pos_pk IS NULL
>
> or using the datetime
>
> SELECT t.top_pk, t.csubject,
> p1.dpost
> FROM tblforumtopic t
> JOIN tblforumpost p1 ON t.top_pk = p1.top_fk
> LEFT JOIN tblforumpost p2 ON p1.top_fk = p2.top_fk AND p2.dpost >
> p1.dpost
> WHERE p2.dpost IS NULL

Also, what's the difference between a topic and a post?
Isn't a topic just the first post in a given thread? In which case you
might consider a structure as follows:

messages(message_id*,parent_id,message,message_dat e)

In this scenario, topics are simply messages with NULL parents.

strawberry wrote:

> Captain Paralytic wrote:
>
> > Beauregard T. Shagnasty wrote:
> >
> > > Here is one that has had me puzzled for a day or so. I've built a simple
> > > online forum on a club web site (not accessible by the public) using
> > > MySQL/PHP, and I cannot make it sort correctly. I have found a lot of
> > > postings and web pages about the topic, but none of the solutions have
> > > worked.
> > >
> > > Two simple tables in a 1-M:
> > >
> > > tblforumtopic
> > > top_pk INTEGER primary key AUTO-INCREMENT
> > > csubject VARCHAR(60) the topic heading
> > > dpost DATETIME when posted
> > >
> > > tblforumpost
> > > pos_pk INTEGER primary key AUTO-INCREMENT
> > > top_fk INTEGER foreign key
> > > mbody TEXT text of the post
> > > dpost DATETIME when posted
> > > .. and a couple of others not relevant here
> > >
> > > I want to display the Topics, with the subject line, the number of posts
> > > in the thread, and the datetime of the *last* post. Here is what I've
> > > been working with, plus many variations based on advice seen 'round,
> > > none of which worked.
> > >
> > > SELECT tblforumtopic.top_pk, tblforumtopic.csubject,
> > > tblforumpost.dpost, COUNT(tblforumpost.pos_pk) AS ncnt
> > > FROM tblforumtopic, tblforumpost
> > > WHERE tblforumpost.top_fk = tblforumtopic.top_pk
> > > GROUP BY tblforumtopic.top_pk
> > > ORDER BY tblforumpost.dpost DESC
> > >
> > > ..which always returns the datetime of the *first* post and not the
> > > last.
> > >
> > > The web server is running MySQL server version: 4.1.21-standard. Any
> > > ideas, please?
> > >
> > >
> > > --
> > > -bts
> > > -Motorcycles defy gravity; cars just suck
> > I assume that the *last* post can be identified by either it having the
> > highest pos_pk value or the most recent dpost datetime for a given
> > topic. In which case this is yet another time when the "Strawberry
> > Query" is what is required. Either one of the following queries should
> > work to get you the datetime of the latest post. You may need a second
> > query to get the count of posts as well, I'll need to play with it a
> > bit to see if it is possible in one JOIN query but if not you can
> > always perform an extra JOIN to a subquery to perform the count:
> >
> > SELECT t.top_pk, t.csubject,
> > p1.dpost
> > FROM tblforumtopic t
> > JOIN tblforumpost p1 ON t.top_pk = p1.top_fk
> > LEFT JOIN tblforumpost p2 ON p1.top_fk = p2.top_fk AND p2.pos_pk >
> > p1.pos_pk
> > WHERE p2.pos_pk IS NULL
> >
> > or using the datetime
> >
> > SELECT t.top_pk, t.csubject,
> > p1.dpost
> > FROM tblforumtopic t
> > JOIN tblforumpost p1 ON t.top_pk = p1.top_fk
> > LEFT JOIN tblforumpost p2 ON p1.top_fk = p2.top_fk AND p2.dpost >
> > p1.dpost
> > WHERE p2.dpost IS NULL
>
> Also, what's the difference between a topic and a post?
> Isn't a topic just the first post in a given thread? In which case you
> might consider a structure as follows:
>
> messages(message_id*,parent_id,message,message_dat e)
>
> In this scenario, topics are simply messages with NULL parents.
In the OP's scenario, the "topic" was the subject and the "post" is the
article. Your structure and suggestion do not have anywhere to put the
subject. Of course, one could have subject included in each record,
which would allow the subject (topic heading) to be changed midway
through the discusion, rather like posts in a USENET newsgroup.

Captain Paralytic wrote:

> strawberry wrote:
>
> > Captain Paralytic wrote:
> >
> > > Beauregard T. Shagnasty wrote:
> > >
> > > > Here is one that has had me puzzled for a day or so. I've built a simple
> > > > online forum on a club web site (not accessible by the public) using
> > > > MySQL/PHP, and I cannot make it sort correctly. I have found a lot of
> > > > postings and web pages about the topic, but none of the solutions have
> > > > worked.
> > > >
> > > > Two simple tables in a 1-M:
> > > >
> > > > tblforumtopic
> > > > top_pk INTEGER primary key AUTO-INCREMENT
> > > > csubject VARCHAR(60) the topic heading
> > > > dpost DATETIME when posted
> > > >
> > > > tblforumpost
> > > > pos_pk INTEGER primary key AUTO-INCREMENT
> > > > top_fk INTEGER foreign key
> > > > mbody TEXT text of the post
> > > > dpost DATETIME when posted
> > > > .. and a couple of others not relevant here
> > > >
> > > > I want to display the Topics, with the subject line, the number of posts
> > > > in the thread, and the datetime of the *last* post. Here is what I've
> > > > been working with, plus many variations based on advice seen 'round,
> > > > none of which worked.
> > > >
> > > > SELECT tblforumtopic.top_pk, tblforumtopic.csubject,
> > > > tblforumpost.dpost, COUNT(tblforumpost.pos_pk) AS ncnt
> > > > FROM tblforumtopic, tblforumpost
> > > > WHERE tblforumpost.top_fk = tblforumtopic.top_pk
> > > > GROUP BY tblforumtopic.top_pk
> > > > ORDER BY tblforumpost.dpost DESC
> > > >
> > > > ..which always returns the datetime of the *first* post and not the
> > > > last.
> > > >
> > > > The web server is running MySQL server version: 4.1.21-standard. Any
> > > > ideas, please?
> > > >
> > > >
> > > > --
> > > > -bts
> > > > -Motorcycles defy gravity; cars just suck
> > > I assume that the *last* post can be identified by either it having the
> > > highest pos_pk value or the most recent dpost datetime for a given
> > > topic. In which case this is yet another time when the "Strawberry
> > > Query" is what is required. Either one of the following queries should
> > > work to get you the datetime of the latest post. You may need a second
> > > query to get the count of posts as well, I'll need to play with it a
> > > bit to see if it is possible in one JOIN query but if not you can
> > > always perform an extra JOIN to a subquery to perform the count:
> > >
> > > SELECT t.top_pk, t.csubject,
> > > p1.dpost
> > > FROM tblforumtopic t
> > > JOIN tblforumpost p1 ON t.top_pk = p1.top_fk
> > > LEFT JOIN tblforumpost p2 ON p1.top_fk = p2.top_fk AND p2.pos_pk >
> > > p1.pos_pk
> > > WHERE p2.pos_pk IS NULL
> > >
> > > or using the datetime
> > >
> > > SELECT t.top_pk, t.csubject,
> > > p1.dpost
> > > FROM tblforumtopic t
> > > JOIN tblforumpost p1 ON t.top_pk = p1.top_fk
> > > LEFT JOIN tblforumpost p2 ON p1.top_fk = p2.top_fk AND p2.dpost >
> > > p1.dpost
> > > WHERE p2.dpost IS NULL
> >
> > Also, what's the difference between a topic and a post?
> > Isn't a topic just the first post in a given thread? In which case you
> > might consider a structure as follows:
> >
> > messages(message_id*,parent_id,message,message_dat e)
> >
> > In this scenario, topics are simply messages with NULL parents.
> In the OP's scenario, the "topic" was the subject and the "post" is the
> article. Your structure and suggestion do not have anywhere to put the
> subject. Of course, one could have subject included in each record,
> which would allow the subject (topic heading) to be changed midway
> through the discusion, rather like posts in a USENET newsgroup.

Well, despite the redundancy of (potentially) duplicated headers, that
seems quite sensible to me.

Captain Paralytic wrote:

> strawberry wrote:
>> Also, what's the difference between a topic and a post? Isn't a topic
>> just the first post in a given thread? In which case you might
>> consider a structure as follows:
>>
>> messages(message_id*,parent_id,message,message_dat e)
>>
>> In this scenario, topics are simply messages with NULL parents.
>
> In the OP's scenario, the "topic" was the subject and the "post" is the
> article.

Correct. topic: parent record, and post: children

> Your structure and suggestion do not have anywhere to put the
> subject. Of course, one could have subject included in each record,

I could, but don't see the need for allowing these club members to
change subjects, which would possibly confuse many of them. No geniuses
they. My two tables are 3NF (I removed the redundant dpost in the topic
table).

> which would allow the subject (topic heading) to be changed midway
> through the discusion, rather like posts in a USENET newsgroup.

Using MySQL (and web authoring) is a hobby for me; I come from a
now-retired career using FoxBase and FoxPro. <g>

--
-bts
-Motorcycles defy gravity; cars just suck

Captain Paralytic wrote:

> Beauregard T. Shagnasty wrote:
> ...
>> I want to display the Topics, with the subject line, the number of
>> posts in the thread, and the datetime of the *last* post. Here is
>> what I've been working with, plus many variations based on advice
>> seen 'round, none of which worked.
>>
>> SELECT tblforumtopic.top_pk, tblforumtopic.csubject,
>> tblforumpost.dpost, COUNT(tblforumpost.pos_pk) AS ncnt
>> FROM tblforumtopic, tblforumpost
>> WHERE tblforumpost.top_fk = tblforumtopic.top_pk
>> GROUP BY tblforumtopic.top_pk
>> ORDER BY tblforumpost.dpost DESC
>>
>> ..which always returns the datetime of the *first* post and not the
>> last.
>
> I assume that the *last* post can be identified by either it having the
> highest pos_pk value or the most recent dpost datetime for a given
> topic.

That would be correct, either the datetime or the auto pk will work.

> In which case this is yet another time when the "Strawberry
> Query" is what is required. Either one of the following queries should
> work to get you the datetime of the latest post. You may need a second
> query to get the count of posts as well, I'll need to play with it a
> bit to see if it is possible in one JOIN query but if not you can
> always perform an extra JOIN to a subquery to perform the count:
>
> SELECT t.top_pk, t.csubject,
> p1.dpost
> FROM tblforumtopic t
> JOIN tblforumpost p1 ON t.top_pk = p1.top_fk
> LEFT JOIN tblforumpost p2 ON p1.top_fk = p2.top_fk AND p2.pos_pk >
> p1.pos_pk
> WHERE p2.pos_pk IS NULL
>
> or using the datetime
>
> SELECT t.top_pk, t.csubject,
> p1.dpost
> FROM tblforumtopic t
> JOIN tblforumpost p1 ON t.top_pk = p1.top_fk
> LEFT JOIN tblforumpost p2 ON p1.top_fk = p2.top_fk AND p2.dpost >
> p1.dpost
> WHERE p2.dpost IS NULL

These two do essentially what my initial attempts did when not COUNTing,
and yes, they do get the last post's date. But not the count. :-(

Been playing with all kinds of ways (many of which crashed <g>), adding
COUNT() in somewhere, and still not successful. I guess I don't know the
proper way, or place, to stuff in the extra JOIN that you mention. The
COUNT() won't work at all without a GROUP BY, which needs to be in there
somewhere.

Thanks for your help.

--
-bts
-Motorcycles defy gravity; cars just suck

Beauregard T. Shagnasty wrote:

> Captain Paralytic wrote:
>
> > Beauregard T. Shagnasty wrote:
> > ...
> >> I want to display the Topics, with the subject line, the number of
> >> posts in the thread, and the datetime of the *last* post. Here is
> >> what I've been working with, plus many variations based on advice
> >> seen 'round, none of which worked.
> >>
> >> SELECT tblforumtopic.top_pk, tblforumtopic.csubject,
> >> tblforumpost.dpost, COUNT(tblforumpost.pos_pk) AS ncnt
> >> FROM tblforumtopic, tblforumpost
> >> WHERE tblforumpost.top_fk = tblforumtopic.top_pk
> >> GROUP BY tblforumtopic.top_pk
> >> ORDER BY tblforumpost.dpost DESC
> >>
> >> ..which always returns the datetime of the *first* post and not the
> >> last.
> >
> > I assume that the *last* post can be identified by either it having the
> > highest pos_pk value or the most recent dpost datetime for a given
> > topic.
>
> That would be correct, either the datetime or the auto pk will work.
>
> > In which case this is yet another time when the "Strawberry
> > Query" is what is required. Either one of the following queries should
> > work to get you the datetime of the latest post. You may need a second
> > query to get the count of posts as well, I'll need to play with it a
> > bit to see if it is possible in one JOIN query but if not you can
> > always perform an extra JOIN to a subquery to perform the count:
> >
> > SELECT t.top_pk, t.csubject,
> > p1.dpost
> > FROM tblforumtopic t
> > JOIN tblforumpost p1 ON t.top_pk = p1.top_fk
> > LEFT JOIN tblforumpost p2 ON p1.top_fk = p2.top_fk AND p2.pos_pk >
> > p1.pos_pk
> > WHERE p2.pos_pk IS NULL
> >
> > or using the datetime
> >
> > SELECT t.top_pk, t.csubject,
> > p1.dpost
> > FROM tblforumtopic t
> > JOIN tblforumpost p1 ON t.top_pk = p1.top_fk
> > LEFT JOIN tblforumpost p2 ON p1.top_fk = p2.top_fk AND p2.dpost >
> > p1.dpost
> > WHERE p2.dpost IS NULL
>
> These two do essentially what my initial attempts did when not COUNTing,
> and yes, they do get the last post's date. But not the count. :-(
>
> Been playing with all kinds of ways (many of which crashed <g>), adding
> COUNT() in somewhere, and still not successful. I guess I don't know the
> proper way, or place, to stuff in the extra JOIN that you mention. The
> COUNT() won't work at all without a GROUP BY, which needs to be in there
> somewhere.
>
> Thanks for your help.
>
> --
> -bts
> -Motorcycles defy gravity; cars just suck
You need to create the count as a sub-query and then JOIN to that.

Try:

SELECT t.top_pk, t.csubject, p1.dpost, tc.ncnt
FROM tblforumtopic t
JOIN tblforumpost p1 ON t.top_pk = p1.top_fk
JOIN (
SELECT tblforumpost.top_fk, COUNT( tblforumpost.pos_pk ) AS ncnt
FROM tblforumpost
GROUP BY tblforumpost.top_fk
) AS tc ON t.top_pk = tc.top_fk
LEFT JOIN tblforumpost p2 ON p1.top_fk = p2.top_fk AND p2.pos_pk >
p1.pos_pk
WHERE p2.pos_pk IS NULL

Captain Paralytic wrote:

> Beauregard T. Shagnasty wrote:
>> <snip>

> You need to create the count as a sub-query and then JOIN to that.
>
> Try:
>
> SELECT t.top_pk, t.csubject, p1.dpost, tc.ncnt

Yes! I was forgetting the "tc.ncnt" placeholder in the first line.

> FROM tblforumtopic t
> JOIN tblforumpost p1 ON t.top_pk = p1.top_fk
> JOIN (
> SELECT tblforumpost.top_fk, COUNT( tblforumpost.pos_pk ) AS ncnt
> FROM tblforumpost
> GROUP BY tblforumpost.top_fk
> ) AS tc ON t.top_pk = tc.top_fk
> LEFT JOIN tblforumpost p2 ON p1.top_fk = p2.top_fk AND p2.pos_pk >
> p1.pos_pk
> WHERE p2.pos_pk IS NULL

This works exactly as desired. My thanks to you for all of this.

Now all I need to do is sit back and wait for the club members to
subscribe. :-0

--
-bts
-Motorcycles defy gravity; cars just suck