GROUP BY clause not working coherently

Report · May 09, 2007

hi:

i have this master page listing some shops with a thumbnail and some data:

http://www.fashiontraveler.com/newsite/sdct_shoplst.php?m=12&brand=242&country=150&city=2406

if you click in the "see more +" link a detail page is loaded showing
more photos and data

the problem is the client wants the thumbnail in the master page to be
the first photo introduced (that with lower id_photo) as listed in the
detail page

i'd say the GROUP BY clause should always return the last photo
corresponding to a shop but as you can see, the first 2 shops show the
last and the other 3 show the first photo as thumbnail!?

"SELECT shops.id_shop,name,address,zip,phone,text,photos_shop.photo
FROM shops
LEFT JOIN photos_shop ON shops.id_shop = photos_shop.id_shop
GROUP BY shops.id_shop
ORDER BY name";

tia,

jdoe

Report · May 09, 2007

You only use the GROUP BY clause when using aggregate functions in your SQL statement. If you want to sort by the ID's lowest to highest (1,2,3,4..) use ORDER BY ID ASC, or highest to lowest (5,4,3,2,1...) ORDER BY ID DESC.

Report · May 10, 2007

>>You only use the GROUP BY clause when using aggregate functions in your
>>SQL
>>statement.

f.t.r. Not strictly true! The GROUP BY clause is very useful with or without
aggregate functions.

"envision3d" <webforumsuser@macromedia.com> wrote in message
news:f1tni4$e8j$1@forums.macromedia.com...
> You only use the GROUP BY clause when using aggregate functions in your
> SQL
> statement. If you want to sort by the ID's lowest to highest (1,2,3,4..)
> use
> ORDER BY ID ASC, or highest to lowest (5,4,3,2,1...) ORDER BY ID DESC.
>

Report · May 10, 2007

i don't agree with you, GROUP BY can be useful w/o aggregate functions

there're several photos for each shop and if i don't use the GROUP BY
clause each shop would be listed as many times as photos it has in the
master page

envision3d wrote:
> You only use the GROUP BY clause when using aggregate functions in your SQL
> statement. If you want to sort by the ID's lowest to highest (1,2,3,4..) use
> ORDER BY ID ASC, or highest to lowest (5,4,3,2,1...) ORDER BY ID DESC.
>

Report · May 10, 2007

>
> "SELECT shops.id_shop,name,address,zip,phone,text,photos_shop.photo
> FROM shops
> LEFT JOIN photos_shop ON shops.id_shop = photos_shop.id_shop
> GROUP BY shops.id_shop
> ORDER BY name";

It's that GROUP BY that's incoherent. The database should have rejected it
outright and thrown an error.
Even so, why would you expect to always get the last photo when you've
ordered by nothing other than the shop name?

In a real GROUP BY, all columns that appear in the SELECT list must also
appear in the GROUP BY clause or inside an aggregate function. Anything
else is incomprehensible gibberish that's subject to the whims of the crazy
DBMS that allows it in the first place.

Report · May 10, 2007

Lionstone wrote:
>> "SELECT shops.id_shop,name,address,zip,phone,text,photos_shop.photo
>> FROM shops
>> LEFT JOIN photos_shop ON shops.id_shop = photos_shop.id_shop
>> GROUP BY shops.id_shop
>> ORDER BY name";
>
> It's that GROUP BY that's incoherent. The database should have rejected it
> outright and thrown an error.
> Even so, why would you expect to always get the last photo when you've
> ordered by nothing other than the shop name?

i tried also ordering by id_photo column w/o success; i guess because
after the GROUP BY there's only 1 photo per shop so ordering has no
effect at all. in fact, what i expect is getting always the same result
but no sometimes the first photo and others the last

BTW, what i need is to get always the first photo

> In a real GROUP BY, all columns that appear in the SELECT list must also
> appear in the GROUP BY clause or inside an aggregate function.

that's true in standard SQL; MYSQL allows not including all columns in
the GROUP BY

> Anything
> else is incomprehensible gibberish that's subject to the whims of the crazy
> DBMS that allows it in the first place.

at least now i know what MySQL is (-:

Report · May 10, 2007

i've found a workaround using a subquery, instead the second table name
in the join, to order the photos ascending by their id. that way the
GROUP BY always choose the first photo when grouping by shop

...
LEFT JOIN (SELECT id_shop,photo FROM photos_shop ORDER BY id_photo) AS
photos
...

John Doe wrote:
> hi:
>
> i have this master page listing some shops with a thumbnail and some data:
>
> http://www.fashiontraveler.com/newsite/sdct_shoplst.php?m=12&brand=242&country=150&city=2406
>
>
> if you click in the "see more +" link a detail page is loaded showing
> more photos and data
>
> the problem is the client wants the thumbnail in the master page to be
> the first photo introduced (that with lower id_photo) as listed in the
> detail page
>
> i'd say the GROUP BY clause should always return the last photo
> corresponding to a shop but as you can see, the first 2 shops show the
> last and the other 3 show the first photo as thumbnail!?
>
> "SELECT shops.id_shop,name,address,zip,phone,text,photos_shop.photo
> FROM shops
> LEFT JOIN photos_shop ON shops.id_shop = photos_shop.id_shop
> GROUP BY shops.id_shop
> ORDER BY name";
>
> tia,
>
> jdoe

Report · May 10, 2007

"John Doe" <dummy@dummy.com> wrote in message
news:f1vu0o$8ku$1@forums.macromedia.com...
> i've found a workaround using a subquery, instead the second table name in
> the join, to order the photos ascending by their id. that way the GROUP BY
> always choose the first photo when grouping by shop
>
> ...
> LEFT JOIN (SELECT id_shop,photo FROM photos_shop ORDER BY id_photo) AS
> photos
> ...
>

That's a good solution, but a better one would be to join on only the
earliest photos:

....
LEFT OUTER JOIN (SELECT id_shop, MIN(photo_id) AS FirstPhotoID FROM
photos_shop GROUP BY id_shop) AS PMIN ON s.id_shop=PMIN.id_shop
LEFT OUTER JOIN photos P ON PMIN.FirstPhotoID=P.photo_id
....

That will even work on databases that make sense. ;)