×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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?

RE: Include a field in "Select" but not in "Group By"

Quote:

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

RE: Include a field in "Select" but not in "Group By"

(OP)
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?

RE: Include a field in "Select" but not in "Group By"

Quote (r937):


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?   

RE: Include a field in "Select" but not in "Group By"

Quote:

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

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

smile

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

RE: Include a field in "Select" but not in "Group By"

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.

RE: Include a field in "Select" but not in "Group By"

Quote:

... 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...


smile

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

RE: Include a field in "Select" but not in "Group By"

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
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"

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.  

RE: Include a field in "Select" but not in "Group By"

"unique indexes do not apply when dealing with NULLs"

???

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

RE: Include a field in "Select" but not in "Group By"

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

Quote:


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. 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. If you specify a prefix value for a column in a UNIQUE index, the column values must be unique within the prefix.

RE: Include a field in "Select" but not in "Group By"

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close