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:253604 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
