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

Alliax damiencola at wanadoo.fr
Tue Jan 8 18:05:22 CST 2002


Hello, as I told you I've just had a problem accessing my site for some days
due to my provider DNS problems, ok I could have tested it locally, but was
busy chasing them and doing other things..

I'll try to remind you my problem, with some sample data:

I have several times the same entries in a table, the difference between the
groups of same entry is the primary key AND the timestamp.
I want to list each group of entries as one line per group. And I want them
to be ordered by chronological order.

here is my query:
$apquestion= mysql_query("SELECT aplink, aplogo, apname, aptime FROM
$aptable GROUP BY aplogo ORDER BY aptime DESC");

it works almost fine as can be seen here:
http://www.lingoparadise.com/toulon/autopub/ap_form.php?vlist=1&sr=202&sg=18
9&sb=135

my problem has been revealed by the 21esite logo, as you can see it seems to
have been first entered pretty recently, the date shows 29/12/01 17:22
but in fact the first entry is from the end of november..

When I look at the mysql table, using phpmyadmin which doesn't order the
table in anyway, I can see this 21esite entry in third position, although
the primary key (automatic incrementing counter) is 53..

So I concluded that my query is doing fine, except that when creating the
GROUP out of aplien, aplogo, apnom and aptime
mysql has to choose one entry to base itself from, so it takes the first
entry in the table. first being in the "natural" order of mysql, the one
revealed by using phpmyadmin to show the table content..

Am I being clear?
Do you know what I should add to my query in order to tell that I'd like the
GROUP BY to choose the entry that has the lowest aptime value ?

Thank you.

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






More information about the thelist mailing list