Re: output listing problem

2004-11-09 Thread Jeff Congdon
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

Re: output listing problem

2004-11-09 Thread DRE
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

Re: output listing problem

2004-11-08 Thread DRE
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

Re: output listing problem

2004-11-05 Thread Deanna Schneider
You just need to alter your order by: order by itemid, date asc The asc on the date won't affect the itemid order at all. - Original Message - From: "DRE" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Thursday, November 04, 2004 2:56 P

Re: output listing problem

2004-11-04 Thread Jerry Johnson
It's 16:00 here in Boston, and there are not many thoughts left at all. I didn't quite understand what you are trying to do. Nor how it is not working now. >From a general standpoint, there is seldom things you can do in local structures that >SQL can't do for you better and faster. (The cf-SQL

Re: output listing problem

2004-11-04 Thread Jeff Congdon
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: >

Re: output listing problem

2004-11-04 Thread Jeff Congdon
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

Re: output listing problem

2004-11-04 Thread DRE
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

output listing problem

2004-11-04 Thread DRE
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 wher