[thelist] SQL Query : using Group By and Order By together

[Alliax]

Bonjour Rudy,

I am afraid you've mistaken my goal, it is not to have several times the
same logo, but to GROUP all entries featuring the aplogo into ONE row.

Using GROUP BY, I have this behavior, and it was working fine up until now
(that is, was displaying chronologically)

But now, I have an entry which features a known aplogo, but somehow using
phpMyAdmin to browse my table, this entry appears in the first page
althought having a primary key set higher than any of the other entries.
Then, I see this entry at the top of my 'gallery' page, whereas it should
have been quite down in the page (as it was before the logo's owner
submitted it again recently)

unfortunately, my host is down at the moment (several hours now..) otherwise
I could have shown you the page.

So it's really an issue about the internal indexing of mysql, or a real big
fault of mine using GROUP BY, isn't it?


ps: you said: "what GROUP BY does is create one row for each value of the
group"
do you mean, as I think _group by_ behaves, it creates one row including all
values of the group ?

Cordialement,

__ Alliax         ~CV : http://LingoParadise.com/cv.php
Un site pour Toulon : http://www.ToulonParadise.com
Un site pour Renaud : http://www.rfaucilhon.com
Un site pour Director : http://www.LingoParadise.com

-----Message d'origine-----
De : thelist-admin at lists.evolt.org
[mailto:thelist-admin at lists.evolt.org]De la part de rudy
Envoyé : samedi 5 janvier 2002 02:25
À : thelist at lists.evolt.org
Objet : Re: [thelist] SQL Query : using Group By and Order By together


>    SELECT aplien, aplogo, apnom, aptime
>         FROM table
>        GROUP BY aplogo
>     ORDER BY aptime DESC
>
> So I concluded that the problem may come from:
> 1. the index of the mysql database, is determining the order of the logo
and
> not my supposed chronological order via "ORDER BY aptime DESC"
> 2. a misunderstanding of the functionning of GROUP BY

bonjour alliax

i am afraid it is #2

what GROUP BY does is create one row for each value of the group

your syntax there is faulty, because the GROUP BY column does not match the
SELECT columns

> In a 'gallery' page my goal is to list all submitted logos, by
> chronological order, but grouping them by the logo's url since people can
> submit several time the same logo..

you do not want GROUP BY, you want

      SELECT aplien, aplogo, apnom, aptime
           FROM table
       ORDER BY aplogo, aptime DESC

if you want to space the groups in your output, you need to do that in php


rudy
http://rudy.ca/