List:General Discussion« Previous MessageNext Message »
From:Adam Date:April 9 2004 11:47am
Subject:Re: Why can't I use an "AS" value in the WHERE clause.
View as plain text  
MIchael, Point well taken.

Cheers,
Adam

On Apr 8, 2004, at 2:47 PM, Michael Stassen wrote:

> 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