List:General Discussion« Previous MessageNext Message »
From:Enrique Sanchez Vela Date:November 9 2007 6:31am
Subject:Re: Group By and IF statement
View as plain text  
--- mysqlman <bozarth@stripped> wrote:

> 
> I am attempting to get a simple query working:
> 
> select *,MAX(a.teaching_date) as max,
> MIN(a.teaching_date) as min from
> teaching a, topic_cat b where a.teaching_topic =
> b.topic_id or
> a.teaching_topic = 999 group by a.teaching_topic
> order by a.teaching_date
> DESC
> 
> This all works, except I don't want to 'group by
> a.teaching_topic' if
> a.teaching_topic = 999.
> 
> Anyone know how to do this?  Thank you.
> -- 

I don't quite understand your problem, do you want the
records with teaching_topic equal to 999 to show up in
the result set or not?

If you want them to show up, the group statement still
applies because the max() and min() function require a
GROUP BY clause.

Now, if instead you want a specific string/date to be
displayed when the teaching_topic equals 999 you could
do...

select a.x, b.y, if(a.teaching = 999,
'1970-01-01',max(a.teaching_topic) as max,
if(a.teaching = 999,
'1970-01-01',min(a.teaching_topic) as min from
teaching a , topic_cat b where a.teaching_topic =
b.topic_id or a.teaching_topic = 999 group by
a.teaching_topic order by a.teaching_date DESC;


regards,
Enrique.

--------------------------------------
"What you have been obliged to discover
by yourself leaves a path in your mind
which you can use again when the need
arises."    --G. C. Lichtenberg

http://themathcircle.org/

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 
Thread
Group By and IF statementmysqlman8 Nov
  • Re: Group By and IF statementyaya sirima8 Nov
  • Re: Group By and IF statementEnrique Sanchez Vela9 Nov