Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.
knela

knela

Programmer
Jan 26, 2007
13
BR
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?
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
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?
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
r937 said:
2. switch to mysql, which is the only database system i know that allows a SELECT column to be omitted from the GROUP BY
Click to expand...

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?
 
Upvote 0 Downvote
Does it randomly pick a value for the non-aggregated, non-grouped column
Click to expand...
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
... may be true for some dbms products, while other dbms products may behave and optimize in different ways
Click to expand...
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
I ran across this after seeing a strange issue with duplicates within a unique index in a table.


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.
Click to expand...
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

BobThornton
  • Locked
  • Question
windows command in a sql script
Replies
1
Views
410
carp
carp
BobThornton
  • Locked
  • Question
Cross Join in an Insert Into
Replies
1
Views
380
carp
carp
sal21
  • Locked
  • Question
SELECT COUNT distinct
Replies
5
Views
442
sal21
sal21
sal21
  • Locked
  • Question
SORT by part of filed 2
Replies
5
Views
511
2009luca
2009luca
BobThornton
  • Locked
  • Question
Count records until a record value changes
Replies
2
Views
560
carp
carp

Part and Inventory Search

Sponsor

Back
Top