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*
>>>
>>>
>>>
>
