GROUP BY clause not working coherently

"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. ;)

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

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 (-:

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

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

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

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.