Hi Jeff, I see what you're trying to get at it but it seems not to work for me. The query works but its not grouping properly in the output statement. Heres why I think that is.
In the group by, you have to have all of the non aggregate fields which include date. Now date is the one that I need outside the group. That is, each item can have multiple dates. So, if date is in the group, then it will create groups of every item/date combination. Then the order by operates on the order date which is the same as date. So, effectively, there is no group. Does this make sense? What have I missed? Thanks in advance. DRE On Thu, 04 Nov 2004 13:07:43 -0800, Jeff Congdon <[EMAIL PROTECTED]> wrote: > sorry that should probably be ASC, not DESC, if you want it to go low-high > > -jc > > > > Jeff Congdon wrote: > > >make your query like this: > > > >SELECT item, date, MIN(date) AS orderDate > >FROM yourtable > >WHERE itemID = date_itemID > >GROUP BY item, date > >ORDER BY orderDate DESC, item, date > > > >-jc > > > >DRE wrote: > > > > > > > >>Nobody has any thoughts? > >> > >> > >>On Thu, 4 Nov 2004 08:14:30 -0800, DRE <[EMAIL PROTECTED]> wrote: > >> > >> > >> > >> > >>>Hi, > >>>I have a tricky little issue. Maybe someone can help me. > >>> > >>>I have a query that returns a grouped output. There are items that > >>>have mutiple dates and those dates have to be in order. > >>> > >>>Kinda like this. > >>> > >>>item z datea dateb datec > >>>item r datee datef dateg > >>> > >>>so my query is like > >>>select item, date > >>>where itemid = date_itemid > >>>order by itemid, date > >>> > >>>And the output looks like this > >>><cfoutput query="me" group="item"> > >>> #item# > >>> <cfoutput> > >>> #date# > >>> </cfoutput> > >>></cfoutput> > >>> > >>>So, in order to keep the dates outputing right, I have to order them > >>>after the item in the sql order by. > >>> > >>>Now my problem is this: I have to have the item with the earliest date > >>>first. However, to keep the cfoutput group working, I have to have > >>>the date by as secondary to the item. > >>> > >>>I'm thinking I can do it by dumping it into an array of structures and > >>>then sorting the array of structures or something like that or perhaps > >>>writing a sp. This page will be hit a lot so I'm trying to keep it > >>>simple and fast. > >>> > >>>cfmx and sql2000. > >>> > >>>Any ideas??? > >>> > >>>Thanks in advance. > >>>DRE > >>> > >>>-- > >>>DRE > >>>www.webmachineinc.com > >>>www.theanticool.com > >>> > >>> > >>> > >>> > >>> > >> > >> > >> > >> > > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183649 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
