MySQL group by question

[Guest]

Sound like a fine idea to me.

 

One question that has bugged me over the past couple of days, maybe you can answer:

 

select

p.products_id,

p.products_image,

max(o.date_purchased) max_date_purchased

from

....

group by

p.products_id

....

 

My understanding is that this is in fact invalid SQL code?

How is your database going to know which one of the p.products_image entries it is going to show, since you are grouping by p.products_id?

How does MySQL deal with, cases like this?

 

 

Cheers,

Rob

[Guest]

Default OSC product record only has one image in it so there is no issue with that query.

[Guest]

I understand that whatever record MySQL chooses, doesn't matter in this case.

My question was slightly more off-topic, how does MySQL handle this in general? Would the query fail in case of different values?

 

But this is off-topic for this forum, so I guess I'll go and find a manual.

 

Cheers,

Rob

[Guest]

No... It should order the records retrieved by products_id...

 

productid_1 record1

productid_1 record2

productid_1 record3

 

productid_2 record1

productid_2 record2

productid_2 record3

 

Group By is to allow you to group records by a common field. Any singular field should be automatically joined to each record that it fits. Try the exact query that you are referring to in phpMyAdmin and look at the results..

[Guest]

Group By is to allow you to group records by a common field. Any singular field should be automatically joined to each record that it fits.

 

I know that, but isn't the 'proper' way to do this to include every field by either in the GROUP BY or used in an aggragate function?

 

Cheers,

Rob

[Daemonj]

Moved to General Chit-Chat by DaemonJ as the more appropriate forum.

[wizardsandwars]

Hi Guys,

 

The reason that the original query is invallid is because *all* of the column not being aggregated must be present in the group by clause.

 

select

p.products_id,

p.products_image,

max(o.date_purchased) max_date_purchased

from

....

group by

p.products_id,

p.products_image,

 

This works GREAT!

[Guest]

That was the point I was trying to make, thanks for the clarification.

 

I was asking because I was running into this problem with my postgresql backend. I was wondering wether I and postgresql were both wrong and that any columns not in the GROUP BY or an aggregate could be handled a third way.

 

But it seems that MySQL is just very forgiving wrt handling improper SQL.

 

Thanks,

Rob

[wizardsandwars]

Yeah, actually, there are a few other DBMS that will allow you to leave out one or more of the non aggregated columns in the where clause.

 

The reason is because of what is called "Table Folding" Table folding is when you purposly create a cartisean product to procuce a result set that includes complicated groupings.

 

Let me know if you would like more information on when 'Table Folding' would be appropriate, and I'll forward you a demonstartation.

[Guest]

The reason is because of what is called "Table Folding" Table folding is when you purposly create a cartisean product to procuce a result set that includes complicated groupings.

Since this thread got moved to chitchat, I won't be bashful.

 

I don't see how leaving out a column in a group by would matter, if the left out column is known to have the same values you could include them in the GROUP BY in the first place, if not the DB has to make some decision on you behalf which makes the result inpredictable so it would seem that you dan't care about the result and leaves me to wonder why you're selecting that column in the first place.

(Performance might be better when the DB just spits out a random result from the GROUPed records for that column?).

 

I did a google on "Table Folding" but I fail to see it's impact/ relevance on column grouping.

 

So, in short, I could probably learn something here, I'd love a deonstration.

 

Cheers,

Rob