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