Mark Stanton <> wrote: > I'm guessing that the new enginebehaviour whereby a group by clause > must include all the fields that are selected is to follow a new SQL > standard, is that right? > > Regardless of the source, why is this considered an improvement? It > seems to me to reduce my options for querying. I can imagine that > it's to "protect coders from themselves", but that's the only excuse > for it that I can come up with, and I don't want to be protected from > myself. > > Any thoughts?
Hey Mark, why are you using the group by? Is it the sum of a column or another aggregate? You man need to do subselects to get what your trying to achieve. Take this simple attempt: SELECT DISTINCT ward_id, MAX(DISTINCT date_modified) AS date_modified, modified_by FROM dbo.HOURS GROUP BY ward_id, modified_by Instead this is probably going to get the answer: select * from dbo.HOURS as h where date_modified in ( select max(date_modified) from dbo.hours where wardid = h.wardid) You just pull the max date in the sub select and tie it's ID to the other rows your looking for. HTH Stephen Russell DBA / .Net Developer Memphis TN 38115 901.246-0159 "A good way to judge people is by observing how they treat those who can do them absolutely no good." ---Unknown http://spaces.msn.com/members/srussell/ No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.5.484 / Virus Database: 269.13.2/983 - Release Date: 9/1/2007 4:20 PM _______________________________________________ Post Messages to: ProFox@leafe.com 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.