Hello Sam. On Fri, Jun 15, 2001 at 03:54:55PM -0700, [EMAIL PROTECTED] wrote: > > When I do the following query I get seemingly arbitrary results for most of the >columns. > > SELECT f.folder_name, COUNT(g.post_id), n.poster_name, p.file_name > FROM users u, folders f, users_to_files g, posts p, posters n > WHERE f.user_id = u.user_id AND u.user_id = 1 AND g.folder_id=f.folder_id > AND p.post_id=g.post_id AND p.poster_id=n.poster_id > GROUP BY f.folder_name This is the expected and documented behaviour: http://www.mysql.com/doc/G/r/Group_by_functions.html > Is it possible to give MySQL ordering commands for the columns that > are collapsed together by a GROUP BY ? > > The above query gives me the below where the ordering of the > selected Poster & Photo seems arbitrary when what I want is Poster > by alpha order ASC and Photo by alpha order ASC and one row per > Folder. > > | Folder | COUNT(g.post_id)| Poster | Photo | > | Animals | 15 | WildAnimalsFan | Zebra.jpg | > | Cars | 34 | TheBMWFan | Z3_BMW.jpg | > Just use MIN(n.poster_name), MIN(p.file_name). > > What I really want is this where the Poster is alpha ASC and the > Photo alpha ASC & there's only one row per Folder : > > | Folder | COUNT(g.post_id)| Poster | Photo | > | Animals | 15 | Animalia | Bear | > | Cars | 34 | AlfaFan | Ferrari | [...] The complete SELECT would look like this: SELECT f.folder_name AS Folder, COUNT(g.post_id) AS Amount, MIN(n.poster_name) AS Poster, REPLACE(MIN(p.file_name),'.jpg','') AS Photo FROM users u, folders f, users_to_files g, posts p, posters n WHERE u.user_id = 1 AND f.user_id = u.user_id AND g.folder_id = f.folder_id AND p.post_id = g.post_id AND p.poster_id=n.poster_id GROUP BY f.folder_name ORDER BY f.folder_name Bye, Benjamin. PS: To all: Presenting a pretty-formatted query increases the chance that someone cares to read it. --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
