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