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*
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]