>- see footer for list info -< So, Paul, did you ever find a solution to the problem you had posed?
/charlie -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Charlie Arehart Sent: Saturday, April 29, 2006 3:50 PM To: 'Coldfusion Development' Subject: RE: [CF-Dev] Grouping >- see footer for list info -< Right, sorry. I was (mistakenly) thinking ahead to the GROUP BY and HAVING which I later mention, if it came to that. Curiously, SQL (in most implementations) doesn't let you simply repeat a select column alias in a HAVING clause. You have to repeat the functions that created the alias. /charlie -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Duncan Cumming Sent: Friday, April 28, 2006 8:21 AM To: [email protected] Subject: RE: [CF-Dev] Grouping >- see footer for list info -< you're correct that Paul would need to order by the dates. But please note he's converting his date parameter using 102, i.e. the date in the DB is in yy.mm.dd format, and doesn't include a time against it. So provided he orders on date the cfoutput group will work. Duncan Cumming New Media Developer Customer Relations Management / Education Fife Council 700 4105 / 01592 414105 >>> [EMAIL PROTECTED] 27/04/2006 21:15:14 >>> >- see footer for list info -< Duncan, I'm afraid you can't recommend the CFOUTPUT GROUP for sure as a solution since using the GROUP attribute relies on the query having been ORDERed BY the column on which you're grouping. Paul's SQL doesn't show that being done. Also, since his SQL shows that incidentdate has a time element to it, if they're ever other than the same time, Paul will also need to add a SELECT column for that date, converted as he did for the where clause, to only have the date portion, and then ORDER the results on that same date portion. All that said, this still won't satisfy your need to just get the top 2. Sure, you could somehow track that in a loop and only output when there are more than 2, but that's asking CFML to do what SQL can do so much better. What you want to do, instead, is consider a COUNT statement as well as use of the GROUP BY and HAVING clauses. Perhaps someone can help with that. I have to run at the moment so can't. Hope that helps you get in the right direction. /charlie -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Duncan Cumming Sent: Thursday, April 27, 2006 5:22 AM To: [email protected] Subject: Re: [CF-Dev] Grouping >- see footer for list info -< <cfoutput query="getIncidents" group="IncidentDate"> #getIncidents.IncidentDate#<br> <cfoutput> #getIncidents.VictimName#<br> </cfoutput> <br> </cfoutput> Duncan Cumming New Media Developer Customer Relations Management / Education Fife Council 700 4105 / 01592 414105 >>> [EMAIL PROTECTED] 27/04/2006 09:53:02 >>> >- see footer for list info -< I have an application which is recording bullying incidents in schools. I have two tables 'Incidents' and 'victim'. For an incident there can be many victims. The incident is stored under date... I want to be able to return all of the incidents for a given date and show the top 2 victims in a table. like this Date Victims 27/04/06 Bill Blogs Bill Gates 28/04/06 Fred Dibnah Marge Simpson etc..... I am having trouble writing the query .... SELECT tblIncidents.IncidentDate, tblVictims.VictimName FROM tblIncidents INNER JOIN tblVictims ON tblIncidents.IncidentIDPK = tblVictims.IncidentIDFK WHERE (tblIncidents.IncidentDate = CONVERT(DATETIME, '2006-04-27 00:00:00', 102)) This returns 27/04/06 Bill Bloggs 27/04/06 Fred Dibnah How do I group the victims under one date? Regards - Paul _______________________________________________ For details on ALL mailing lists and for joining or leaving lists, go to http://list.cfdeveloper.co.uk/mailman/listinfo -- CFDeveloper Sponsors:- >- Hosting provided by www.cfmxhosting.co.uk -< >- Forum provided by www.fusetalk.com -< >- DHTML Menus provided by www.APYCOM.com -< >- Lists hosted by www.Gradwell.com -< >- CFdeveloper is run by Russ Michaels, feel free to volunteer your help -<
