Can I use Group By for this...??

[anler]

Hi everyone, first of all, sorry for my english...
I'm in this situation:
I have a table in my database named products with the fields id and model in it. Now, I need to get 'all' the products but with different model names and each result represent a valid product.
I have tried with:

select id, modelname, ... from products group by modelname

but I want to be sure on that because later I'll be using the result as follows:

Product.buy result[1]

got what I mean? :S

thanks

[rch1231]

try this:

select id, modelname, description, other stuff 
from products 
order by modelname, description
group by modelname

you have to put it in order before you can group it.

[anler]

Thanks, I will try it out!

[tesuji]

I have a table in my database named products with the fields id and model in it. Now, I need to get 'all' the products but with different model names and each result represent a valid product.
I have tried with:
select id, modelname, ... from products group by modelname

you can't have a list of columns so as to select and only a subset of those columns listed in the group by clause. In such case all columns to select must also be itemized in group by clause, for example:

select a, b from t group by a, b;  -- correct
select a, b, c from t group by a, b;  -- wrong
select a, b from t group by b;  -- also wrong
select a, b, sum(c) from t group by a, b;  -- but this correct

-- tesu

[anler]

Hi, I don't understand why all columns in select must appear in group by. Can you give some references where I can't read without boder you?
Also, I've been trying the following queries in mysql and seems to work fine so I'm a little lost with this:

select Product.title, Product.modelname, Color.code as color 
from products as Product
left join brands as Brand on(Product.brand_id = Brand.id)
left join colors as Color on (Product.color_id = Color.id)
where Brand.code = 'JOMA' GROUP BY Product.modelname;

That shows me an output like this:

+-----------+-----------+--------+-------+
| title | modelname | code | color |
+-----------+-----------+--------+-------+
| BAMBOO | BAMBOO | 130723 | AZ |
| CHAMPION | CHAMPION | 126745 | AZ |
...

Then, if I commit this query:

select Product.title, Product.modelname, Product.code, Color.code as color
from products as Product 
left join brands as Brand on (Product.brand_id = Brand.id)
left join colors as Color on(Product.color_id = Color.id) 
where Brand.code = 'JOMA' and Product.code = 130723;

The output is:

+--------+-----------+--------+------+
| title | modelname | code | color|
+--------+-----------+--------+------+
| BAMBOO | BAMBOO | 130723 | AZ |
+--------+-----------+--------+------+

and those output seems to be equals :/
Any idea?

Thanks to both

[tesuji]

Hi,

Yes, I know, MySQL indeed did this insane expansion of the mathematically well defined select-order-by clause. Now everybody will be able to use MySQL for comparing apples to oranges without cease. Now you should, no, you must carefully examine every row result whether it contains idiotic combinations of invalid column values or not.

Let me allow to cite from chapter 11.16.3 of MySQL reference manual:

"MySQL extends the use of GROUP BY so that you can use nonaggregated columns or calculations in the select list that do not appear in the GROUP BY clause."

And further on this warning:

"When using this feature, all rows in each group should have the same values for the columns that are ommitted from the GROUP BY part. The server is free to return any value from the group, so the results are indeterminate unless all values are the same."

Do you got it? Indeed, MySQL pulled off a feat to turn the complete relational algebra and set theorie topsy-turvy.

NEVER EVER use this foolish MySQL feature!

-- tesu

[anler]

Thanks a lot, your explanation was great. I get it now ... finally!! :)