Here you go Alan.
DROP TABLE IF EXISTS `profilepicts`; CREATE TABLE `profilepicts` ( `ProfilePictsID` int(10) NOT NULL auto_increment, `ProfileID` int(10) default NULL, `ProfileScrapbookID` int(10) default NULL, `ImageName` varchar(255) default NULL, `Title` varchar(255) default NULL, `Caption` varchar(255) default NULL, `Rating` int(10) default NULL, `VoteTotal` int(10) default NULL, `Votes` int(10) default NULL, `DateAdded` datetime default NULL, `Winner` tinyint(1) default NULL, `WinnerDate` datetime default NULL, `Live` tinyint(1) NOT NULL default '0', PRIMARY KEY (`ProfilePictsID`), KEY `ProfileID` (`ProfileID`) ) ENGINE=MyISAM AUTO_INCREMENT=61 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC; INSERT INTO `profilepicts` VALUES (28,2,3,'Niagara Falls 161.jpg','scrapbooktest2','test2',15,30,2,'2009-05-28 00:00:00',NULL,NULL,1); INSERT INTO `profilepicts` VALUES (32,2,4,'Mom and Dads HT speakers 003.jpg','aaaaaaaddddddddddd','sdffffffffffff',11,44,4,'2009-05-28 16:07:09',NULL,NULL,1); INSERT INTO `profilepicts` VALUES (34,2,3,'Mom and Dads HT speakers 001.jpg',NULL,NULL,18,317,18,NULL,1,'2009-06-09 16:00:55',1); INSERT INTO `profilepicts` VALUES (36,2,0,'Tommy 011.jpg','asdfsafsad','adsfdsafs',11,32,3,'2009-05-29 15:04:15',NULL,NULL,1); INSERT INTO `profilepicts` VALUES (37,2,0,'Tommy 010.jpg','adggggg','ggg',14,159,11,'2009-05-29 15:05:44',NULL,NULL,1); INSERT INTO `profilepicts` VALUES (38,2,0,'Tommy 008.jpg','hhhhhhhhh','hhhhhhhhhhh',NULL,NULL,NULL,'2009-05-29 15:09:24',NULL,NULL,1); INSERT INTO `profilepicts` VALUES (39,2,6,'Tommy 009.jpg','qqqqqqqq','qqqqq',19,19,1,'2009-05-29 15:10:53',NULL,NULL,1); INSERT INTO `profilepicts` VALUES (44,1,9,'12060004.JPG','asfdsaf','sadfsadfa',NULL,NULL,NULL,'2009-06-01 10:19:58',1,'2009-06-08 15:53:46',1); INSERT INTO `profilepicts` VALUES (45,2,6,'120600041.JPG','asfsad','fsadf',NULL,NULL,NULL,'2009-06-01 12:17:24',NULL,NULL,1); INSERT INTO `profilepicts` VALUES (46,2,6,'120600061.JPG','aewrewrw','ewqrewqrw',NULL,NULL,NULL,'2009-06-01 12:17:33',NULL,NULL,1); INSERT INTO `profilepicts` VALUES (47,6,10,'2ndary-page.jpg','asdfas','dfsadf',NULL,NULL,NULL,'2009-06-01 13:32:04',NULL,NULL,1); INSERT INTO `profilepicts` VALUES (48,6,10,'fashion-hot-spots.jpg','adsfsa','fdsaf',NULL,NULL,NULL,'2009-06-01 13:32:12',1,'2009-06-07 15:53:46',1); INSERT INTO `profilepicts` VALUES (49,3,11,'fmf.jpg','adsf','asdf',14,222,16,'2009-06-01 13:33:22',NULL,NULL,1); INSERT INTO `profilepicts` VALUES (50,5,12,'2ndary-pageAlt.jpg','adsfsa','dfsadfs',15,106,7,'2009-06-01 13:34:07',NULL,NULL,1); INSERT INTO `profilepicts` VALUES (51,1,13,'Epcot 2.JPG','test','test',10,10,1,'2009-06-04 09:38:41',NULL,NULL,1); INSERT INTO `profilepicts` VALUES (52,2,3,'Western Dinner 5.JPG','mmmmm good','asfdadsfds',NULL,NULL,NULL,'2009-06-08 13:46:03',NULL,NULL,1); INSERT INTO `profilepicts` VALUES (57,2,5,'C6664A4D-F9A1-27CD-57CF0B4879648111.jpg','test','test',NULL,NULL,NULL,'2009-06-09 15:02:21',1,'2009-06-04 15:53:46',1); INSERT INTO `profilepicts` VALUES (58,2,5,'C66690B6-97AD-2367-59D50409E1DEFD9F.jpg','asdf','asdf',NULL,NULL,NULL,'2009-06-09 15:02:39',NULL,NULL,1); INSERT INTO `profilepicts` VALUES (59,2,4,'CBC4042D-C5CA-9147-D58B03C59ED55B39.jpg','test','test',NULL,NULL,NULL,'2009-06-10 16:02:49',NULL,NULL,1); INSERT INTO `profilepicts` VALUES (60,3,14,'CBE190BC-F941-5BBB-7988A145B999530C.jpg','test','test',NULL,NULL,NULL,'2009-06-10 16:35:06',NULL,NULL,1); Here are the queries I was questioning: SELECT PP.ProfileID FROM profilepicts PP WHERE PP.Live = 1 ORDER BY PP.DateAdded DESC RESULTS: 3 2 2 2 1 5 3 6 I add a distinct and I get SELECT DISTINCT(PP.ProfileID) FROM profilepicts PP WHERE PP.Live = 1 ORDER BY PP.DateAdded DESC RESULTS: 5 3 6 1 2 Why did 5 come first after I added the DISTINCT? > -----Original Message----- > From: Alan Rother [mailto:[email protected]] > Sent: Wednesday, June 10, 2009 6:02 PM > To: cf-talk > Subject: Re: mysql and order by datetime > > > Hey Chad, > Out of curiosity, can you share those date/time values with us for the > first > query? I'd be interested in playing with it to see why it's doing what > it's > doing. > > > =] > > -- > Alan Rother > Adobe Certified Advanced ColdFusion MX 7 Developer > Manager, Phoenix Cold Fusion User Group, AZCFUG.org > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:323390 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
