vikram8jp Posted February 13, 2009 Share Posted February 13, 2009 MySql server version: 5.0.67 MySql code: to be figured out MySql Errors: NA Table structure: CREATE TABLE `topics` ( `topic_id` bigint(20) unsigned NOT NULL auto_increment, `subject` text collate utf8_unicode_ci NOT NULL, PRIMARY KEY (`topic_id`) ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci CREATE TABLE `posts` ( `post_id` bigint(20) unsigned NOT NULL auto_increment, `topic_id` bigint(20) unsigned NOT NULL auto_increment, `post_time` TIMESTAMP, `body` text collate utf8_unicode_ci NOT NULL, PRIMARY KEY (`post_id`) ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci Explain Output of query: NA Description: I am making a simple bulletin board in my application. I want to find out the last post in every topic and display the time in front of topic. My problem: Consider this data: topic ---------------------------- topic_id ================================================ what is mysql ----------------- 1 what is php -------------------- 2 ================================================ body ---------------------------- post_id ------- topic_id ------- post_time ========================================================= mysql is database ------------ 1 --------------- 1 --------------- 22/10/09 11:30 AM mysql is cool ------------------ 2 --------------- 1 --------------- 22/10/09 12:10 PM mysql is great ----------------- 3 --------------- 1 --------------- 22/10/09 13:15 PM php is web language -------- 4 --------------- 2 --------------- 13/11/09 09:30 AM php is too cool ---------------- 5 --------------- 2 --------------- 13/11/09 11:10 AM I like php ----------------------- 6 --------------- 2 --------------- 13/11/09 11:30 AM ========================================================== The query should return this..... topic ---------------------------- topic_id ------- last_post ================================================ what is mysql ----------------- 1 --------------- 22/10/09 13:15 PM what is php -------------------- 2 -------------- 13/11/09 11:30 AM ================================================ What I've tried: SELECT * FROM `qa_sessions_tb` q1 INNER JOIN `qa_posts_tb` q2 USING ( session_id ) GROUP BY q1.session_id HAVING q2.posts_id = ( SELECT posts_id FROM qa_posts_tb GROUP BY session_id HAVING posted_at = MAX( posted_at ) ) LIMIT 0 , 30 This gave wrong result. Can somebody help me out? How do you think phpfreaks displays last post on forum home page? Thanks and Regards Vikram Quote Link to comment https://forums.phpfreaks.com/topic/145062-problem-in-making-a-query-using-subqueries/ Share on other sites More sharing options...
vikram8jp Posted February 14, 2009 Author Share Posted February 14, 2009 Let it be I have solved it. Never get any answers here anyway. Here is the query I was looking for. It's damn easy, just didn't strike me. SELECT t.topic, t.topic_id, p.post_id, p.post_time FROM topics t INNER JOIN posts p ON t.topic_id = p.topic_id GROUP BY t.opic_id Quote Link to comment https://forums.phpfreaks.com/topic/145062-problem-in-making-a-query-using-subqueries/#findComment-761771 Share on other sites More sharing options...
corbin Posted February 14, 2009 Share Posted February 14, 2009 Never get any answers here anyway? 58 minutes is too long to wait? Quote Link to comment https://forums.phpfreaks.com/topic/145062-problem-in-making-a-query-using-subqueries/#findComment-761784 Share on other sites More sharing options...
vikram8jp Posted February 14, 2009 Author Share Posted February 14, 2009 You missed the AM/PM difference there, corbin. It was 12 hours 58 minutes. Anyway, the way I solved it was also wrong.... This is wrong.... I missed the MAX() aggregate function first of all... my apologies. SELECT t.topic, t.topic_id, p.post_id, [u][b]MAX[/b][/u](p.post_time) FROM topics t INNER JOIN posts p ON t.topic_id = p.topic_id GROUP BY t.opic_id Secondly, that too doesn't give absolutely correct result. My problem: Consider this data: topic ---------------------------- topic_id ================================================ what is mysql ----------------- 1 what is php -------------------- 2 ================================================ body ---------------------------- post_id ------- topic_id ------- post_time ========================================================= mysql is database ------------ 1 --------------- 1 --------------- 22/10/09 11:30 AM mysql is cool ------------------ 2 --------------- 1 --------------- 22/10/09 12:10 PM mysql is great ----------------- 3 --------------- 1 --------------- 22/10/09 13:15 PM php is web language -------- 4 --------------- 2 --------------- 13/11/09 09:30 AM php is too cool ---------------- 5 --------------- 2 --------------- 13/11/09 11:10 AM I like php ----------------------- 6 --------------- 2 --------------- 13/11/09 11:30 AM ========================================================== The query should return this..... topic ---------------------------- topic_id ------- post_id ------ last_post ===================================================== what is mysql ----------------- 1 --------------- 3 -------------- 22/10/09 13:15 PM what is php -------------------- 2 --------------- 6 -------------- 13/11/09 11:30 AM ====================================================== Its giving.... topic ---------------------------- topic_id ------- post_id ------ last_post ===================================================== what is mysql ----------------- 1 --------------- 1 -------------- 22/10/09 13:15 PM what is php -------------------- 2 --------------- 4 -------------- 13/11/09 11:30 AM ====================================================== Can anybody help me out with this? Thanks and Regards Vikram Quote Link to comment https://forums.phpfreaks.com/topic/145062-problem-in-making-a-query-using-subqueries/#findComment-761834 Share on other sites More sharing options...
corbin Posted February 14, 2009 Share Posted February 14, 2009 This is basically a guess, but try: SELECT t.topic, t.topic_id, p.post_id, MAX(p.post_time) FROM topics t INNER JOIN posts p ON t.topic_id = p.topic_id GROUP BY t.topic_id, p.post_id; The problem is because of the group by, so grouping by another column might make it work. Quote Link to comment https://forums.phpfreaks.com/topic/145062-problem-in-making-a-query-using-subqueries/#findComment-761881 Share on other sites More sharing options...
vikram8jp Posted February 14, 2009 Author Share Posted February 14, 2009 Thanks Corbin That didn't work correctly. This query displays all the records. It does not do something like displaying one post per topic, let alone finding the post which has maximum post time. ??? Really, this query seems quite do-able and the logic seems to be around the corne. ??? This is basically a guess, but try: SELECT t.topic, t.topic_id, p.post_id, MAX(p.post_time) FROM topics t INNER JOIN posts p ON t.topic_id = p.topic_id GROUP BY t.topic_id, p.post_id; The problem is because of the group by, so grouping by another column might make it work. Quote Link to comment https://forums.phpfreaks.com/topic/145062-problem-in-making-a-query-using-subqueries/#findComment-761950 Share on other sites More sharing options...
corbin Posted February 14, 2009 Share Posted February 14, 2009 Ahhh, yeah, I should have seen that one coming... since that's the whole point of group by. Hrmmm... A sub query would work, but the performance on that would not be optimal. A cheap hack would be to store the last activity date in the topics table and just update it when ever something happens as far as posts go. Quote Link to comment https://forums.phpfreaks.com/topic/145062-problem-in-making-a-query-using-subqueries/#findComment-762261 Share on other sites More sharing options...
Phoenix~Fire Posted February 15, 2009 Share Posted February 15, 2009 select forum.*,count(forum_content.id) as topics from forum left join forum_content on forum_content.forum=forum.id group by forum.id; that's code I just used in my forum, seems to work quite well... I'm pretty sure you need pretty much the same thing. I'm lazy, so you'll have to modify it for your script on your own. PS Quote Link to comment https://forums.phpfreaks.com/topic/145062-problem-in-making-a-query-using-subqueries/#findComment-762519 Share on other sites More sharing options...
vikram8jp Posted February 16, 2009 Author Share Posted February 16, 2009 @Phoenix~Fire Hello there. Your query will work fine man, but only in the case of COUNT aggregate function. If I replace COUNT with MAX then it will cause the problem that I have stated in my third post of this topic. Thanks and Regards Vikram select forum.*,count(forum_content.id) as topics from forum left join forum_content on forum_content.forum=forum.id group by forum.id; that's code I just used in my forum, seems to work quite well... I'm pretty sure you need pretty much the same thing. I'm lazy, so you'll have to modify it for your script on your own. PS Quote Link to comment https://forums.phpfreaks.com/topic/145062-problem-in-making-a-query-using-subqueries/#findComment-763064 Share on other sites More sharing options...
Phoenix~Fire Posted February 16, 2009 Share Posted February 16, 2009 Interesting.... I have MAX working just fine using 3 joined tables, as well as count, greater and a whole bunch of other aggrevate functions. Basically I got bored Try: select topics.*,max(posts.post_time) as time from topics left join posts on posts.topic_id=topics.topic_id group by topic.id; Works fine on my DB. Quote Link to comment https://forums.phpfreaks.com/topic/145062-problem-in-making-a-query-using-subqueries/#findComment-763173 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.