Re: sorting the average

here is the query: select priv_user.uid, priv_user.fname, priv_user.lname, priv_user.username, avg(article_comment.vote) as avg, IFNULL(avg(article_comment.vote), 3) as avg, count(article_comment.id) as total, article_data.aid, article_data.time, article_data.name, article_data.description from article_data, priv_user LEFT JOIN article_comment on article_comment.aid = article_data.aid where released > 1 and text LIKE \'%'.$_GET['search'].'%\' GROUP BY article_data.aid order by avg desc $_GET['search'] can be anything. I have just tried it in php my admin. seems it it is returning two avg coloumns I used the term blah in this case: uid fname lname username avg avg total aid time name description 1 JJ Harrison Lizner NULL 3 0 10 1029843819 Write The Article's Title Here Write The Article's Description Here 1 JJ Harrison Lizner NULL 3 0 2 1025201600 Real Test 2 Dis is a Real Test 2 BLah BLah BLah BLah 1 JJ Harrison Lizner NULL 3 0 8 1029838185 TESTING TESTING 123 Testing 1... 2... 3... 1 JJ Harrison Lizner 5.0000 5.0000 1 5 1029408033 iuytd rey 1 JJ Harrison Lizner 3.6667 3.6667 3 1 1018101600 Test This a *real* test! Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah I need to merge them or something... -- JJ Harrison webmaster@tececo.com www.tececo.com -- Please reply on the list/newsgroup unless the reply it OT. "Miles Thompson" <milesthompson@ns.sympatico.ca> wrote in message news:20020824005555.UHZE19319.simmts4-srv.bellnexxia.net@there... > > Why not show the complete SELECT? > Why are you trying to have two fields with the same name? > Have you tried this at the mysql console? > > Just to double-check, that second line returns a value of 3 if the > average of article_comment.vote is null. > > Have you tried GROUP BY in place of ORDER? > > Just some thoughts - Miles > > On Friday 23 August 2002 09:46 pm, JJ Harrison wrote: > > I have these two lines in my SELECT clause thingy: > > > > avg(article_comment.vote) as avg, > > IFNULL(avg(article_comment.vote), 3) as avg > > > > when I sort the later with this: > > > > order by avg desc > > > > It only sorts the ones with an average rating take from > > article_comment.vote. > > > > The ones with no votes just stay at the top no matter wheather I say: order > > by avg desc or: order by avg asc. > > > > When I swap them the ones at the bottom will switch into the right order.