Hello Benjamin.
I forgot to mention that there could be items from multiple posters in each folder so
the query you sent worked on those cases just like the old query. However looking at
your nicely formatted example made me realize what I needed to do.
By concatenating the poster_name in front of the post_subject with a CONCAT & then
doing a MIN I was able to get the desired query because now that column is sorted by
Poster_Name,Post_Subject. With PHP I will need to split the Poster_Name from before
the Post_Subject but that's fine.
So you're right nice query formatting can make all the difference !
thanks,
- Sam.
New Query :
SELECT f.folder_name AS Folder,
COUNT(g.post_id) AS Amount,
MIN(concat(n.poster_name,"\n",p.file_name)) AS Poster_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
- Sam.
Benjamin Pflugmann <[EMAIL PROTECTED]> wrote: 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.
---------------------------------
Do You Yahoo!?
Yahoo! Buzz Index - Spot the hottest trends in music, movies,and more.
