The test page works OK with a "www." added: http://www.fclounge.cfdeveloper.co.uk/test.cfm
> -----Original Message----- > From: Matt Williams [mailto:[EMAIL PROTECTED] > Sent: 20 September 2006 14:59 > To: CF-Talk > Subject: Re: How to get these query results - Take 2 > > Rich, you are right in that I want the event with the furthest/newest > date to show first. But if there is another date associated with that > newest event, it should also show under its title. Your solution looks > like it will work. I'll give it a try and let you know. > > I am hoping to find a "bit more clever" solution too as this is > actually an OO-style app where I hope to have a CFC return the query > results for me. I suppose I could just build a new query with QueryNew > instead of the output as you show. > > BTW, your test.cfm page wouldn't load for me. Server not found error. > > Thanks, > Matt > > On 9/20/06, RichL <[EMAIL PROTECTED]> wrote: > > Matt > > > > You can try the following. It seems to work... my gut feeling tells me > > there may be an easier way with less queries but I can't think of > > anything right now. You may want to consider caching queries for speed > > depending on how much the data is changing. > > > > <!--- get all event details ---> > > <cfquery name="getAllEvents" datasource="#request.dsn#" > > cachedwithin="#createTimeSpan(0,1,0,0)#" result="a"> > > select * > > from tblEventName evName > > inner join tblEventDate evDate > > on evName.eventID = evDate.eventID > > </cfquery> > > > > <!--- get the order of the events ordered by the ones that have the > > furthest dates first ---> > > <cfquery name="getOrder" datasource="#request.dsn#" > > cachedwithin="#createTimeSpan(0,1,0,0)#" result="b"> > > select eventname,tbleventdate.eventid, max(eventdate) as > tempdate > > from tbleventdate > > inner join tbleventname > > on tbleventdate.eventid = tbleventname.eventid > > group by eventname, tbleventdate.eventid > > order by max(eventdate) desc, tbleventdate.eventid > > </cfquery> > > > > <!--- test debug ---> > > <cfdump var="#getAllEvents#"> > > <cfdump var="#a#"> > > <cfdump var="#getOrder#"> > > <cfdump var="#b#"> > > > > <!--- output the query with the order of correct eventids ---> > > <cfoutput query="getOrder"> > > > > <h5>#eventname#</h5> > > > > <!--- do a QoQ to get the eventDate details for this event id -- > -> > > <cfquery dbtype="query" name="getEventDetails"> > > select eventDate > > from getAllEvents > > where eventID = #eventID# > > order by eventDate desc > > </cfquery> > > > > <!--- output all events dates for this event id ---> > > <cfloop query="getEventDetails"> > > #dateformat(eventDate,"m/dd/yyyy")#<br /> > > </cfloop> > > > > </cfoutput> > > > > You can see the results here which I think is where you are trying to > get to? : > > > > http://fclounge.cfdeveloper.co.uk/test.cfm > > > > My feeling is that there is something a bit more clever can be done > > with SQL/grouping but this may give you something to work with ? > > > > Rich > > > > On 9/20/06, Matt Williams <[EMAIL PROTECTED]> wrote: > > > I'm gonna try this again as while I slept last night my previous post > > > turned into a discussion of SQL best practices and did not give the > > > answer needed. But that's probably because I didn't explain well > > > enough. Ordering by EventID will not work. Nor will ordering by date. > > > Maybe a little more data will help. > > > > > > EventID, EventName > > > 1 | TestEventOne > > > 2 | TestEventTwo > > > 3 | AThirdEvent > > > > > > EventID | EventDates > > > 1 | 9/10/2006 > > > 1 | 9/20/2006 > > > 2 | 9/15/2006 > > > 3 | 9/30/2006 > > > > > > <!--- Desired results ---> > > > AThirdEvent > > > 9/30/2006 > > > > > > TestEventOne > > > 9/20/2006 > > > 9/10/2006 > > > > > > TestEventTwo > > > 9/15/2006 > > > > > > If you order by EventID, you would get 1, 2, 3. If you ordered by Date > > > DESC, you would get > > > 3 > > > 1 > > > 2 > > > 1 > > > But I want the TestEventOne outputs to be together. GroupBy won't do > > > it because you've ordered by date. > > > > > > This probably isn't possible without at least two queries, or at least > > > some QofQ usage. I just can't seem to get my head around the solution > > > though. > > > > > > -- > > > Matt Williams > > > "It's the question that drives us." > > > > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:253605 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
