Stephen the Cook wrote: > 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 >
A probable use you are thinking of would be select EmployeeId, EmpName, sum(salary) ; from Employee ; group by EmployeeId This would have sense but it is easily done with : select EmployeeId, max(EmpName) as 'EmpName', sum(salary) ; from Employee ; group by EmployeeId HTH _______________________________________________ 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.
