Re: sorting the average

I fixed the syntax of my query: select priv_user.uid, priv_user.fname, priv_user.lname, priv_user.username, 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 no probs now "Jj Harrison" <webmaster@tececo.com> wrote in message news:20020824030637.62797.qmail@pb1.pair.com... > 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. > >