The only way I found to fix this is to return the first query via MySQL then use a Query of a Query in CF to do a group by on the data and ORDER it by the DateTime field.
Strange..... SELECT PP.ProfileID FROM profilepicts PP WHERE PP.Live = 1 ORDER BY PP.DateAdded DESC <cfquery dbtype="query" name="profiles"> SELECT ProfileID FROM profiles GROUP BY ProfileID ORDER BY DateAdded DESC </cfquery> RESULT: 3 2 1 5 6 > -----Original Message----- > From: Chad Gray [mailto:cg...@careyweb.com] > Sent: Wednesday, June 10, 2009 5:13 PM > To: cf-talk > Subject: mysql and order by datetime > > > This is strange. I run this query and I get proper results (DateAdded is > a DateTime data type): > > 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? > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:323376 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
