sql & asp question?

[angst]

Hello,
I'm working on a building a small forum for my web site,
right now a have a list of topics, showsing the topic name and the last person to post and the date,
in the data base i have one table for topics, and one for posts,

what i want to know is how can i order my topics list by the last post?

thanks in advance for your time!

[miranda]

2 ways to do it. 1 is have a field with datatype of date/time insert the date and time with each posting then use an ORDER BY on that field to show the latest posting.

2nd is to use an autonumber field and then use the ORDER BY clause on that field.

To group similar posts together use a GROUP BY clause on another field that will show the parent message of each message posted

[angst]

the posts table does have the datetime for each record,

just not sure how to use the order by form another table then the one i'm looping ( the topics table )
also i'm using mysql,
could you show me an example?

thanks again for your time!
ken

[raf]

why don't you post the table-layout so we can just write the query? Just make an dumpfile and paste the structure-part of the two tables.

you will need to usa a group by with a max() on the datecolumn, and hen order on that. To get the topic data, you'l need to join the two tables.

But from an operational point of view, you should have some database denormalisation and update a 'lastpost' field inside your topic table, so that you don't need to do the agragation at runtime --> since this is a query that will be ran very frequently, it will be better to have this redundant variable inside the topic table, then to spoil runtime resources on the agregations and sorting.
This means you'll need to update this variable in the corresponding row of the topic-table each time a post is added. Normally, in other db-formats, that would be done with a stored procedure. mySQL doesn't support SP yet (version 5 will) So you will just need to deal with it inside your application.

[angst]

CREATE TABLE `ForumPosts` (
`MID` int(4) NOT NULL auto_increment,
`CID` int(11) default NULL,
`UID` int(11) default NULL,
`POST` longtext,
`PostDate` varchar(255) NOT NULL default '',
`TopicID` int(11) NOT NULL default '0',
`EmailReply` tinyint(4) NOT NULL default '0',
`AcctStatus` tinyint(4) default '0',
`PostType` tinyint(4) NOT NULL default '0',
PRIMARY KEY (`MID`)
) TYPE=MyISAM;

#
# Dumping data for table 'ForumPosts'
#

INSERT INTO `ForumPosts` VALUES (97,1,78,'test','4/20/2004 9:45:47 AM',0,0,2,0);
INSERT INTO `ForumPosts` VALUES (98,1,78,'test','4/20/2004 9:45:47 AM',31,0,2,0);
INSERT INTO `ForumPosts` VALUES (95,2,78,'Hello. Where do I go to set this up? I\'ve uploaded a .jpg and .mp3 successfully, now I\'d like to put it all together. \r\nTHX','4/20/2004 9:45:15 AM',0,0,2,0);
INSERT INTO `ForumPosts` VALUES (96,2,78,'Hello. Where do I go to set this up? I\'ve uploaded a .jpg and .mp3 successfully, now I\'d like to put it all together. \r\nTHX','4/20/2004 9:45:26 AM',0,0,2,0);

#
# Table structure for table 'ForumTopics'
#

CREATE TABLE `ForumTopics` (
`TID` int(11) NOT NULL auto_increment,
`UID` int(11) default NULL,
`CID` int(11) default NULL,
`TopicName` varchar(60) default NULL,
`TopicType` tinyint(4) default NULL,
`TopicDate` varchar(255) default NULL,
`ViewCount` tinyint(10) NOT NULL default '0',
`AcctStatus` tinyint(4) NOT NULL default '0',
PRIMARY KEY (`TID`)
) TYPE=MyISAM;

#
# Dumping data for table 'ForumTopics'
#

INSERT INTO `ForumTopics` VALUES (32,78,1,'errors trying to setup',0,'4/20/2004 9:46:52 AM',53,2);
INSERT INTO `ForumTopics` VALUES (31,78,1,'test',0,'4/20/2004 9:45:47 AM',14,2);
INSERT INTO `ForumTopics` VALUES (30,30,1,'Welcome!',0,'4/20/2004 8:45:40 AM',67,1);
INSERT INTO `ForumTopics` VALUES (34,79,4,'interesting link',0,'4/20/2004 12:52:07 PM',28,5);
INSERT INTO `ForumTopics` VALUES (36,64,5,'HTML in profiles?',0,'4/20/2004 3:10:29 PM',29,2);



thanks again for your time!
ken

[raf]

Here you go :

SELECT MAX(TopicDate) as lastdate, forumtopics.TopicName, forumposts.TopicID
FROM forumposts INNER JOIN forumtopics ON forumposts.TopicID=forumtopics.TID
GROUP BY forumtopics.TID
ORDER BY lastdate DESC

I've tested it on the small sample, but i had to change the TopicID values because three of them were 0 which isn't realy possible

[angst]

that did the trick!
thanks!! :thumbsup:

[raf]

You're welcome! :thumbsup: