Thanks to Jeff, the issue is resolved. For the curious, here is the solution.
SELECT A.item, A.date, (select MIN(date) FROM yourTable B ON b.itemID = A.itemID) AS orderDate FROM yourTable A ORDER BY orderDate, A.item, A.date DRE On Mon, 8 Nov 2004 10:45:39 -0800, DRE <[EMAIL PROTECTED]> wrote: > 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:183811 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
