Thanks for the response Ted, I do apologise for the "Deviant" tag though. <BG>
Well at least the discussion has made us re-look at the ways we have "always" done things and I ended up using Peter's neat suggestion of: cursor.Date_Field-Day(cursor.Date_Field)+1 in order to get the first day of the month. Nice one Peter. Dave Crozier -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ted Roche Sent: 26 June 2007 16:13 To: [email protected] Subject: Re: SQL "Group By" error?? On 6/26/07, Dave Crozier <[EMAIL PROTECTED]> wrote: > Also as a slight deviation, Ted Hey! I resemble that! > suggested that the "Select all" is > inappropriate and I'd ask why please? I certainly have always used it within > joins to no adverse effect and can't see why this is wrong - Ted, please > elucidate ??? Well, you've always used it, but to what effect? I've never used it, so it looked out of place to me. The only place I see an ALL having an effect is on a UNION command. The authoritative reference (well, beside's Tamar's SQL book ;) on msdn says: SELECT [ALL | DISTINCT] [TOP nExpr [PERCENT]] Select_List_Item [, ...] FROM [FORCE] Table_List_Item [, ...] [[JoinType] JOIN DatabaseName!]Table [[AS] Local_Alias] [ON JoinCondition [AND | OR [JoinCondition | FilterCondition] ...] [WITH (BUFFERING = lExpr)] [WHERE JoinCondition | FilterCondition [AND | OR JoinCondition | FilterCondition] ...] [GROUP BY Column_List_Item [, ...]] [HAVING FilterCondition [AND | OR ...]] [UNION [ALL] SELECTCommand] [ORDER BY Order_Item [ASC | DESC] [, ...]] [INTO StorageDestination | TO DisplayDestination] [PREFERENCE PreferenceName] [NOCONSOLE] [PLAIN] [NOWAIT] > I thought that you either used distinct or all as they are > obviously mutually exclusive regardless of the group by clause. One man's "obviously..." I sit corrected. You can use ALL or omit it to the same effect, I believe. I've never used it that way. Sorry, wild goose chase. Your further answers have made your original question clearer. If you're dealing with reasonably small data sets so the performance implications are negligible, I'd suggest using a UDF of FDOM(Date) - that's First Day of Month, per the suggestions above - just for clarity: SELECT FDOM(Test.Date) AS TheDate, SUM(Test.nValue) as Column_Value FROM Test GROUP BY TheDate SORT BY TheDate INTO CURSOR cur_All -- Ted Roche Ted Roche & Associates, LLC http://www.tedroche.com [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
