Page 1 of 1

SELECT DISTINCT

Posted: Tue Apr 18, 2006 1:21 pm
by evilmonkey
Hello,

I have a problem with select distinct. Basically, I have a simple forum, and I'm trying to get the topics that have recently been added to. I have three tables, one for the forums, one for the threads, one for the posts (all of them are linked by keys). Their names are 'forums', 'forums_topics', and 'forums_posts'. I am trying to select the 4 most recently active threads, and my quesry is as follows:

Code: Select all

"SELECT DISTINCT(`intopic`) AS `intopic` FROM `forums_posts` ORDER BY `postdate` DESC LIMIT 4"
However, what this does it is selects the latest threads that have only post in them (i.e., it negates all the most recent posts that belong to the same thread.) Is my code wrong, or is my database acting up, as I'm increasingly getting the feeling. By the way, this is true for when this query goes through my scripts, and phpmyadmin. I'm on shared hosting, so I can't query the db directly.

Re: SELECT DISTINCT

Posted: Tue Apr 18, 2006 2:00 pm
by feyd
evilmonkey wrote:However, what this does it is selects the latest threads that have only post in them (i.e., it negates all the most recent posts that belong to the same thread.) Is my code wrong, or is my database acting up, as I'm increasingly getting the feeling. By the way, this is true for when this query goes through my scripts, and phpmyadmin. I'm on shared hosting, so I can't query the db directly.
I'm not quite understanding this whole bit. Could you reword it?

Posted: Tue Apr 18, 2006 2:30 pm
by evilmonkey
Alright, an example:

thread id's: 1,3,4,1,5,2,5,9

Assuming these are sorted from most recently changed to earliest changed, I want the result like this: 1,3,4,5. Instead, my query returns 3,4,2,9. Since 1 and 5 repeat, the query negates them altogether. Hope that makes things clearer. :)

Posted: Tue Apr 18, 2006 3:09 pm
by feyd
I don't see how it's filtering out 1 and 5. Table structures and an exact query would likely help.

SHOW CREATE TABLE

Posted: Tue Apr 18, 2006 3:15 pm
by evilmonkey
You asked for it...good thing it's a small forum. :P This is the forums_posts table.

Code: Select all

-- 
-- Table structure for table `forums_posts`
-- 

CREATE TABLE `forums_posts` (
  `id` bigint(20) NOT NULL auto_increment,
  `message` text NOT NULL,
  `postedby` int(11) NOT NULL default '0',
  `postdate` bigint(20) NOT NULL default '0',
  `intopic` bigint(20) NOT NULL default '0',
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=200 ;

-- 
-- Dumping data for table `forums_posts`
-- 

INSERT INTO `forums_posts` VALUES (133, 'When does the sex start?', 109, 1144538825, 32);
INSERT INTO `forums_posts` VALUES (134, 'So, how long until it picks up around here?', 109, 1144539525, 28);
INSERT INTO `forums_posts` VALUES (122, 'Few changes on the forum:\r\n\r\n*Icons work, try locking something (if you''re Vlad)\r\n*I made an announcement system. Announcements have thier own icon, and always stay at the top of the thread. Admins and Moderators can post articles directly from the "new post" window.\r\n*Moderators have the ability to unlock threads (not just lock them)\r\n*Number of replies is fixed\r\n*Username is linked\r\n<br><br><font size = "-4">Last edited by Vitali on April 06, 2006 a	 11:10 AM</font>', 1, 1144337663, 29);
INSERT INTO `forums_posts` VALUES (115, 'Whoa, I think I'm the first visitor! I'm making history here. Cool!', 101, 1144285832, 28);
INSERT INTO `forums_posts` VALUES (116, '[quote=Sercher]Whoa, I think I'm the first visitor! I'm making history here. Cool![/quote]\r\n\r\nYup...you can own this slice of history...till I delete this topic. :D', 1, 1144287175, 28);
INSERT INTO `forums_posts` VALUES (136, 'the site is coming out april 21... im expecting around 1k ppl, within the first week... lots of hot girls :eek:  :eek:  :eek: ', 2, 1144539924, 28);
INSERT INTO `forums_posts` VALUES (137, 'welcome to tmeet. the more bugs you report, the more people will like this site, the more friends they bring, some of whom will be hot girls, threby increasing your chances. so start reporting bugs. :P', 1, 1144546827, 32);
INSERT INTO `forums_posts` VALUES (128, 'Furthermore:\r\n\r\n*icon size is fixed\r\n*users can no longer edit thier own posts (sorry guys). Only mods and admins can edit.<br><br><font size = "-4">Last edited by Vitali on April 06, 2006 a	 1:45 PM</font>', 1, 1144349082, 29);
INSERT INTO `forums_posts` VALUES (138, '[quote=Vlad]the site is coming out april 21... im expecting around 1k ppl, within the first week... lots of hot girls :eek:  :eek:  :eek: [/quote]\r\n\r\nNOOOOO! Dude...that was suppossed to be an internal date....DAMN!  :meh: Oh well...now we just have to work harder.  :lol: <br><br><font size = "-4">Last edited by Vitali on April 08, 2006 a	 8:43 PM</font>', 1, 1144546978, 28);
INSERT INTO `forums_posts` VALUES (118, 'Sorry Ian, you were two posts late. :P', 1, 1144295660, 28);
INSERT INTO `forums_posts` VALUES (139, 'If you're interested in a release date, Vlad went and revealed it here: [url]http://www.tmeet.com/forum/thread.php?threadid=28#136[/url]', 1, 1144547135, 32);
INSERT INTO `forums_posts` VALUES (140, 'hot girls eh? i have a gf :8o: <br><br><font size = "-4">Last edited by Vlad on April 10, 2006 a	 11:56 PM</font>', 105, 1144614547, 28);
INSERT INTO `forums_posts` VALUES (141, ' ;)  ;)  ;)  ;)  ;)  ;) ', 2, 1144618752, 28);
INSERT INTO `forums_posts` VALUES (143, 'hahahahhahahahahahahaha', 103, 1144633418, 32);
INSERT INTO `forums_posts` VALUES (146, 'Yo you spelt Ontario wrong on the locations pull down menu', 110, 1144637542, 32);
INSERT INTO `forums_posts` VALUES (147, '[quote=Mikebob]Yo you spelt Ontario wrong on the locations pull down menu[/quote]\r\n\r\nThat sucks ass. I'll fix it ASAP. Besides, it was Vlad that did it, not me. :P', 1, 1144639104, 32);
INSERT INTO `forums_posts` VALUES (149, '[quote=Vitali][quote=Mikebob]Yo you spelt Ontario wrong on the locations pull down menu[/quote]\r\n\r\nThat sucks ass. I'll fix it ASAP. Besides, it was Vlad that did it, not me. :P[/quote]\r\n\r\nIt's been fixed. :)', 1, 1144639518, 32);
INSERT INTO `forums_posts` VALUES (150, ' ;) haha what does that mean?', 103, 1144701619, 28);
INSERT INTO `forums_posts` VALUES (153, 'Hey Paul...don''t be a douche. :P You''re coming on Thursday, right?\r\n\r\nOh, I found and fixed another tiny bug on the forum :D<br><br><font size = "-4">Last edited by Vitali on April 10, 2006 a	 9:30 PM</font>', 1, 1144722368, 28);
INSERT INTO `forums_posts` VALUES (162, 'Who can find the new feature I added to the forum tonight? Hint: it''s not something you can''t change...at least not at will.\r\n\r\nEnjoy.<br><br><font size = "-4">Last edited by Vitali on April 10, 2006 a	 9:44 PM</font>', 1, 1144723471, 33);
INSERT INTO `forums_posts` VALUES (163, 'We all know that Vlad is an aswsome webdesigner, and I'm a great web programmer. However, we suck at spelling. (one of us more than the other :P ) So let this topic contain a list of words that are misspelled around the site. If you see something spelled wrong, come here and post it, and DO NOT PM me. I'll start:\r\n\r\nIn the viewtopics page, "Replys" should be "Replies".', 1, 1144723828, 34);
INSERT INTO `forums_posts` VALUES (117, 'What about me?  :I ', 102, 1144293054, 28);
INSERT INTO `forums_posts` VALUES (114, 'Welcome, everyone, to Toronto's best hangout portal. [b]tmeet[/b] is definitely all that and more. The forum has no been wiped, so feel free to start chatting. Don't worry, we'll delete all this too before the formal launch. ;)\r\n\r\nVitali.', 1, 1144278444, 28);
INSERT INTO `forums_posts` VALUES (178, 'nice work :smokin: ', 2, 1144731431, 28);
INSERT INTO `forums_posts` VALUES (179, 'In the Company page, under Latest News, "Luanched" should be "Launched". ', 101, 1144779515, 34);
INSERT INTO `forums_posts` VALUES (180, 'guys i added a google video to my html box... any1 can do they same by going into [u]edit my profile -> html box[/u] :lol:  :lol: <br><br><font size = "-4">Last edited by Vlad on April 11, 2006 a	 7:02 PM</font>', 2, 1144800125, 37);
INSERT INTO `forums_posts` VALUES (181, 'I put a funny animation in mine. :)', 1, 1144803655, 37);
INSERT INTO `forums_posts` VALUES (182, 'what a ****** 0] <br><br><font size = "-4">Last edited by Vlad on April 12, 2006 a	 9:48 PM</font>', 105, 1144805031, 28);
INSERT INTO `forums_posts` VALUES (183, 'Hey,\r\nI scanned through most of the ebsite and found a lot of mistakes...I'll post them up here for correction...\r\n\r\nOn the Company page\r\n-->latest news-->grammar mistake after TMEET [u]is has[/u]? and "luanched" to "launched"\r\n\r\nOn the Sign-up page\r\n-->Parental permission-->"TEET" to "TMEET"\r\n-->ownership and copyright-->comma after "download"\r\n                          -->delete repeated word "publish"\r\n-->limited liability-->change the last sentence to "However, TMEET will investigate any complaints that may arise from posted material or pictures that violate this agreement."\r\n-->changing the agreement-->change "these" to "this"\r\n\r\nOn the Search page-->"[u]terms of use[/u]" to "[u]Terms of Use[/u]"\r\n\r\nAlso at the very bottom of the Home page--yellow type--"advertisments" should be changed to "advertisements" (missing an "e")\r\n\r\nOtherwise great site :D  :D  :D ', 9, 1144808735, 34);
INSERT INTO `forums_posts` VALUES (184, 'I just noticed a couple more mistakes  :lol: \r\n\r\nOn the Edit my Profile page-->instructions-->2nd paragraph, "then proceed in clicking" should be "then proceed by clicking"\r\n\r\nAs well, "advertisement" is mispelled on multiple pages, not just the home page  :D ', 9, 1144809202, 34);
INSERT INTO `forums_posts` VALUES (185, 'ummm...when i move my cursor over the smilies, the definitions are incorrect...just another error i thought i should bring up...\r\n\r\nand the smoking smilie is repeated twice...don't know if that's intentional? lol', 9, 1144809377, 34);
INSERT INTO `forums_posts` VALUES (186, '[quote=TOO_HOT]ummm...when i move my cursor over the smilies, the definitions are incorrect...just another error i thought i should bring up...\r\n\r\nand the smoking smilie is repeated twice...don't know if that's intentional? lol[/quote]\r\n\r\nOh <span style='color:blue' title='I&#39;m naughty, are you naughty?'>smurf</span>...yeah, i gott fix those descriptions...or take them out altogether, it's not like they do any good. Vlad, did you duplicate the smoking smilie? I remeber removing one extra smilie....', 1, 1144810783, 34);
INSERT INTO `forums_posts` VALUES (187, 'What the hell! There hasn't been a good movie since December at least. Someone just asked me to reccommend aa movie, and after going to the cineplex site, all i could reccommend is to stay home and save some money. Come on, Hollywood...you owe us!', 1, 1144812038, 38);
INSERT INTO `forums_posts` VALUES (188, 'i didnt... its all ur fault ;)  ;) <br><br><font size = "-4">Last edited by Vlad on April 11, 2006 a	 11:37 PM</font>', 2, 1144816639, 34);
INSERT INTO `forums_posts` VALUES (191, 'i fixed the problem, when u mouse over the smilies everything is fine<br><br><font size = "-4">Last edited by Vlad on April 12, 2006 a	 12:10 AM</font>', 2, 1144818607, 34);
INSERT INTO `forums_posts` VALUES (195, 'someone has TERRIBLE grammar...hmmm wonder who it is  :lol: \r\n\r\nSomeone put "in clicking" instead of "by clicking" in picture upload page...', 9, 1144948603, 34);
INSERT INTO `forums_posts` VALUES (196, '[quote=TOO_HOT]someone has TERRIBLE grammar...hmmm wonder who it is  :lol: \r\n\r\nSomeone put "in clicking" instead of "by clicking" in picture upload page...[/quote]\r\n\r\nAin't me. :lol:', 1, 1144957565, 34);
INSERT INTO `forums_posts` VALUES (198, 'yo\r\n', 105, 1145314639, 32);
INSERT INTO `forums_posts` VALUES (199, 'Hey Mike  :smokin: ', 1, 1145324752, 32);
This is my query:

Code: Select all

SELECT DISTINCT (
`intopic`
) AS `intopic`
FROM `forums_posts`
ORDER BY `postdate` DESC
LIMIT 4
And this is the result:

Code: Select all

Edit 	Delete 	38
	Edit 	Delete 	37
	Edit 	Delete 	34
	Edit 	Delete 	33
Hope that helps. :)

Posted: Tue Apr 18, 2006 3:46 pm
by feyd
I see what the problem is now. The order by happens after the selection takes effect. You can see this illustrated when you do this:

Code: Select all

mysql> SELECT `intopic`, `postdate` FROM `forums_posts` GROUP BY `intopic` ORDER BY `postdate` DESC;
+---------+------------+
| intopic | postdate   |
+---------+------------+
|      38 | 1144812038 |
|      37 | 1144800125 |
|      34 | 1144723828 |
|      33 | 1144723471 |
|      28 | 1144539525 |
|      32 | 1144538825 |
|      29 | 1144337663 |
+---------+------------+
7 rows in set (0.00 sec)

mysql> SELECT `intopic`, `postdate` FROM `forums_posts` ORDER BY `postdate` DESC;
+---------+------------+
| intopic | postdate   |
+---------+------------+
|      32 | 1145324752 |
|      32 | 1145314639 |
|      34 | 1144957565 |
|      34 | 1144948603 |
|      34 | 1144818607 |
|      34 | 1144816639 |
|      38 | 1144812038 |
|      34 | 1144810783 |
|      34 | 1144809377 |
|      34 | 1144809202 |
|      34 | 1144808735 |
|      28 | 1144805031 |
|      37 | 1144803655 |
|      37 | 1144800125 |
|      34 | 1144779515 |
|      28 | 1144731431 |
|      34 | 1144723828 |
|      33 | 1144723471 |
|      28 | 1144722368 |
|      28 | 1144701619 |
|      32 | 1144639518 |
|      32 | 1144639104 |
|      32 | 1144637542 |
|      32 | 1144633418 |
|      28 | 1144618752 |
|      28 | 1144614547 |
|      32 | 1144547135 |
|      28 | 1144546978 |
|      32 | 1144546827 |
|      28 | 1144539924 |
|      28 | 1144539525 |
|      32 | 1144538825 |
|      29 | 1144349082 |
|      29 | 1144337663 |
|      28 | 1144295660 |
|      28 | 1144293054 |
|      28 | 1144287175 |
|      28 | 1144285832 |
|      28 | 1144278444 |
+---------+------------+
39 rows in set (0.00 sec)
As you can see, the selection is finding the oldest post first. Which makes sense since they are the first in the table.

Suggestion: store the last post's timestamp in the thread record.

Posted: Tue Apr 18, 2006 3:55 pm
by evilmonkey
feyd wrote:Suggestion: store the last post's timestamp in the thread record.
Hmm...I was hoping to avoid that, but looks like I'll have to do it. Thanks for the help. :)