|
|
Posted by Rik on 07/17/06 15:45
Sutha Balasubramaniam wrote:
Please don't toppost.
>>> Can someone please tell me what's wrong with this query?
>>>
>>> mysql_query("SELECT a,b,c,d,e FROM my_table WHERE d!='0' && a='1'
>>> GROUP BY b ORDER BY c DESC LIMIT 21");
>>>
>> It's probably the GROUP BY clause.
>> If there are several possibilities for c,d & e where b is the same,
>> the server doesn't know what to show.
>>
>> Test:
>> mysql_query("SELECT a,b FROM my_table WHERE a='1' GROUP BY b LIMIT
>> 21"); and:
>> mysql_query("SELECT a,b,c,d,e FROM my_table WHERE d!='0' && a='1'
>> GROUP BY b ORDER BY c DESC LIMIT 21");
>> To see if the rest of the query is OK.
> Thanks for your response. With:
>
> mysql_query("SELECT a,b,c,d,e FROM my_table WHERE d!='0' && a='1'
> GROUP
> BY b
> ORDER BY c DESC LIMIT 21");
>
> I see the query now, however it does not do the 'ORDER BY c DESC'
> part! Basically, it doesn't sort in DESC order! Any ideas?
By default, MySQL sorts all GROUP BY col1, col2, . queries as if you
specified ORDER BY col1, col2, . in the query as well. Use EXPLAIN SELECT...
to check how the query is run.
It might be necessary to nest the query (or use a temporary table):
mysql_query("SELECT * FROM (SELECT a,b,c,d,e FROM my_table WHERE d!='0' &&
a='1' GROUP BY b) ORDER BY c DESC LIMIT 21");
But that's getting complicated, and with a overhead which might be avoided.
I'm sure there are some mysql guru's who can explain exactly what happens
here.
Grtz,
--
Rik Wasmus
Navigation:
[Reply to this message]
|