Heh, except the ON in the subquery should be a WHERE, as pointed out by DRE
SELECT A.item, A.date, (select MIN(date) FROM yourTable B WHERE b.itemID = A.itemID) AS orderDate FROM yourTable A ORDER BY orderDate, A.item, A.date -Jeff DRE wrote: >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:183813 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=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
