Include a field in "Select" but not in "Group By"
Include a field in "Select" but not in "Group By"
(OP)
Hello guys! I would like to include a field in a select but don't use it with group by (forgive my english...). This is my select:
"SELECT NumSol,NumCot,NumPed,ObsSol FROM E405Sol WHERE CODEMP = nCodEmp AND NUMCOT = nNumCot AND CODPRO = aCodPro AND CODDER = aCodDer GROUP BY NumCot,NumPed,NumSol"
When I run it I get a message telling that "ObsSol" needs to be include in "group by". What can I do?
"SELECT NumSol,NumCot,NumPed,ObsSol FROM E405Sol WHERE CODEMP = nCodEmp AND NUMCOT = nNumCot AND CODPRO = aCodPro AND CODDER = aCodDer GROUP BY NumCot,NumPed,NumSol"
When I run it I get a message telling that "ObsSol" needs to be include in "group by". What can I do?
RE: Include a field in "Select" but not in "Group By"
1. not much
or
2. switch to mysql, which is the only database system i know that allows a SELECT column to be omitted from the GROUP BY
r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
RE: Include a field in "Select" but not in "Group By"
Another way, perhaps, is to use the DISTINCT predicate and to get rid of the GROUP BY clause.
Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: Include a field in "Select" but not in "Group By"
RE: Include a field in "Select" but not in "Group By"
Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: Include a field in "Select" but not in "Group By"
I was unaware of this, seems strange. How does MySQL handle this? Does it randomly pick a value for the non-aggregated, non-grouped column, or does it implicitly group on it?
RE: Include a field in "Select" but not in "Group By"
when i first ran across this feature a few years ago, i couldn't help but think it was an egregious error on the part of the mysql engineers
i have come to change my thinking completely, and now i think it has advantages
(of course, the disadvantage is quite noticeable -- novice mysql developers who don't really understand grouping get fouled up on it all the time)
have a read of this article, which is quite long but ~very~ worth the time...
Debunking GROUP BY myths
r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
RE: Include a field in "Select" but not in "Group By"
If the DBMS has the ability to verify functionally dependent columns, it's good practice to write partial GROUP BY clauses! (A good optimizer is supposed to detect those columns anyway.)
But too many (less experienced) users have selected random data without knowing it. So as long as the DBMS can't detect (and prohibit) non-functionally dependent columns, stick with the old "select list columns must either be an argument to an aggregate function, or be referenced in the group by clause"-rule.
BTW, the "partial GROUP BY list can result in better performance" argument may be true for some dbms products, while other dbms products may behave and optimize in different ways.
RE: Include a field in "Select" but not in "Group By"
in the case of mysql, the article makes perfect sense to me
as for selecting random data (for non-aggregated, non-grouped columns), this, too, can be considered a feature
how would you do it (pick random data) in your dbms of choice? it's a common enough requirement, but it's not that easy, is it -- most people just default it to MIN() or something...
r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
RE: Include a field in "Select" but not in "Group By"
SELECT f.film_id, f.title ... GROUP BY f.film_id, f.title
vs.
SELECT f.film_id, MAX(f.title) AS title ... GROUP BY f.film_id
Regarding picking random data, is it really a common requirement? I rarely never pick random data. Usually I know what I want and ask for it. In the rare case that any value would do, I just use max (or min). I'd say its a better feature to avoid accidentally random data.
RE: Include a field in "Select" but not in "Group By"
RE: Include a field in "Select" but not in "Group By"
???
r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
RE: Include a field in "Select" but not in "Group By"
http:/
RE: Include a field in "Select" but not in "Group By"
interesting that NULLs are ~not~ considered unique in the GROUP BY clause, eh?
r937.com | rudy.ca
Buy my new book Simply SQL from Amazon