Include a field in "Select" but not in "Group By" 1

[knela]

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?

 

[r937]

What can I do?

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

 

[PHV]

Why not simply use MIN(ObsSol) or MAX(ObsSol) ?

Another way, perhaps, is to use the DISTINCT predicate and to get rid of the GROUP BY clause.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[knela]

Thanks guys, but I can't change my database system because all of the rest is working perfectly. I can't use MIN() or MAX() because "ObsSol" is a text column. However, how can I use DISTINCT? Can You give a example PHV?

 

[PHV]

SELECT DISTINCT NumSol,NumCot,NumPed,ObsSol FROM E405Sol WHERE CODEMP = nCodEmp AND NUMCOT = nNumCot AND CODPRO = aCodPro AND CODDER = aCodDer"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[RiverGuy]

2. switch to mysql, which is the only database system i know that allows a SELECT column to be omitted from the 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?

 

[r937]

Does it randomly pick a value for the non-aggregated, non-grouped column

yes


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

 

[JarlH]

Just finished the article. I must say I do not completely aggree.

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.

 

[r937]

... may be true for some dbms products, while other dbms products may behave and optimize in different ways

sure, but which other dbms products do you know that actually support that functionality?


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

 

[JarlH]

Sorry for being a bit vague and confusing things, thats what happens when I try to read and write comments while I work. My performance comment was regarding the two cases
[tt]SELECT f.film_id, f.title ... GROUP BY f.film_id, f.title[/tt]
vs.
[tt]SELECT f.film_id, MAX(f.title) AS title ... GROUP BY f.film_id[/tt]


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.

 

[RiverGuy]

Interesting article, thanks for the link. I rarely work with MySQL, but this makes the second odd thing I've learned about it this week. The first thing was that unique indexes do not apply when dealing with NULLs.

 

[r937]

unique indexes do not apply when dealing with NULLs"

???

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[RiverGuy]

I ran across this after seeing a strange issue with duplicates within a unique index in a table.

http://dev.mysql.com/doc/refman/5.0/en/create-index.html

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. [red]This constraint does not apply to NULL values except for the BDB storage engine. For other engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL.[/red] If you specify a prefix value for a column in a UNIQUE index, the column values must be unique within the prefix.

 

[r937]

ah, yes, i see

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