List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:April 8 2004 6:47pm
Subject:Re: Why can't I use an "AS" value in the WHERE clause.
View as plain text  
Good point.  I was focused on the question of using the alias to restrict 
results, so I left the function in the SELECT part.  As you say, in this 
query, that would just give a useless column of '1's, so you might as well 
leave it out.  In that case, though, the alias question is moot.  That is, 
it doesn't really make any difference whether you put the condition in the 
WHERE or HAVING clause.

On the other hand, we can imagine a query where we want to see a calculated 
result and use it to screen which rows are returned.  Then using the alias 
in the HAVING clause is the way to go.  For example, something like

   SELECT *, unix_timestamp()-unix_timestamp(last_seen) AS Last_Active
   FROM wifi_table
   HAVING Last_Active < 600;

Michael

Adam wrote:

> Mike,
> 
> I see what you're saying `active` was the alias name not an actual 
> column. Ironically I was using a HAVING clause because I agree with that 
> last post.
> 
> Mike, why keep the `IF` statement? You're really saying give me all the 
> records where this expression is true. Why not just move the expression 
> in the `IF` to the HAVING clause?
> 
> So take my old statement and ditch the where clause. You'll get:
> 
> SELECT *
>    FROM wifi_table
>  HAVING unix_timestamp()-unix_timestamp(last_seen) < 600;
> 
> A little easier on the eyes no?
> 
> Cheers,
> Adam
> 
> On Apr 6, 2004, at 9:42 PM, Michael Stassen wrote:
> 
>> Adam,
>>
>> That won't work.  Daevid doesn't have a column named active.  Nor does 
>> he have to do the math twice.  As was pointed out earlier, he can do 
>> what he wants using HAVING instead of WHERE, like this:
>>
>>   SELECT *,
>>   IF(((unix_timestamp()-unix_timestamp(last_seen)) < 600),1,0) active
>>   FROM wifi_table
>>   HAVING active = 1;
>>
>> Michael
>>
>> Adam wrote:
>>
>>> Daevid,
>>> SELECT *
>>>     FROM wifi_table
>>>  WHERE active = 1
>>>  HAVING unix_timestamp()-unix_timestamp(last_seen) < 600;
>>> Regards,
>>> Adam
>>> On Apr 5, 2004, at 8:29 PM, Daevid Vincent wrote:
>>>
>>>> I'm curious when will I be able to do something like this:
>>>>
>>>> SELECT *, IF(( (unix_timestamp()-unix_timestamp(last_seen)) < 
>>>> 600),1,0) as
>>>> active FROM wifi_table WHERE active = 1;
>>>>
>>>> It's so obnoxious, especially since I can do this:
>>>>
>>>> SELECT *, IF(( (unix_timestamp()-unix_timestamp(last_seen)) < 
>>>> 600),1,0) as
>>>> active FROM wifi_table WHERE 
>>>> unix_timestamp()-unix_timestamp(last_seen) <
>>>> 600;
>>>>
>>>> Why do I have to do the math TWICE?!
>>>>
>>>> *sigh*
>>
>>
>>
> 
> 

Thread
Why can't I use an "AS" value in the WHERE clause.Daevid Vincent6 Apr
  • Re: Why can't I use an "AS" value in the WHERE clause.Paul DuBois6 Apr
    • Re: Why can't I use an "AS" value in the WHERE clause.Joe Rhett6 Apr
    • Re: Why can't I use an "AS" value in the WHERE clause.(Pete Harlan)6 Apr
      • Re: Why can't I use an "AS" value in the WHERE clause.Michael Stassen7 Apr
  • Re: Why can't I use an "AS" value in the WHERE clause.Matt W6 Apr
  • Re: Why can't I use an "AS" value in the WHERE clause.Adam7 Apr
    • Re: Why can't I use an "AS" value in the WHERE clause.Michael Stassen7 Apr
      • Re: Why can't I use an "AS" value in the WHERE clause.Adam8 Apr
        • Re: Why can't I use an "AS" value in the WHERE clause.Michael Stassen8 Apr
          • Re: Why can't I use an "AS" value in the WHERE clause.Adam10 Apr