mysql select group

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");

This returns nothing! Appreciate any helps.

Re: mysql select group

Sutha Balasubramaniam wrote:
> 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");
>
> This returns nothing! Appreciate any helps.

echo mysql_error();

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.

Grtz,
--
Rik Wasmus

Re: mysql select group

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?

Thanks again for helps.

Rik wrote:
> Sutha Balasubramaniam wrote:
> > 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");
> >
> > This returns nothing! Appreciate any helps.
>
> echo mysql_error();
>
> 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.
>
> Grtz,
> --
> Rik Wasmus

Re: mysql select group

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

Re: mysql select group

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");

Im no wizz kid but shouldnt it be

mysql_query("SELECT a,b,c,d,e FROM my_table WHERE d!='0' AND a='1' GROUP
BY b ORDER BY c DESC LIMIT 21");

notice the AND instead of &&


"Sutha Balasubramaniam" wrote in message
news:1153146171.610867.236810@i42g2000cwa.googlegroups.com.. .
> 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");
>
> This returns nothing! Appreciate any helps.
>