Re: Why can't I use an "AS" value in the WHERE clause.
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* -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why can't I use an "AS" value in the WHERE clause.
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]
Re: Why can't I use an "AS" value in the WHERE clause.
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]
Re: Why can't I use an "AS" value in the WHERE clause.
Pete Harlan wrote: On Mon, Apr 05, 2004 at 08:03:33PM -0500, Paul DuBois wrote: At 17:29 -0700 4/5/04, 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; I think you'll never be able to do it. The stuff after the SELECT is calculated based on the rows selected by the WHERE. The WHERE therefore cannot be based on the stuff after the SELECT. The parser has seen the 'as', though, and could expand it in the where clause so the user doesn't have to do it (and do it correctly, and maintain it in parallel). The problem is MySQL can't just start doing this without breaking queries that depend on it not happening. (If the 'as' alias is the same as a field name, for example.) So I doubt it will happen, but not because it couldn't be done. --Pete That's all true, but even ignoring the consequences, it's probably not worth the effort, because the alias does work in a HAVING clause, as Matt W already pointed out. Furthermore, as Paul says, a function of a column cannot be used to decide which rows to look at, because you have to look at the rows to calculate the value of the function. Hence, there is no advantage to putting the calculated condition in the WHERE clause relative to putting it in the HAVING clause. That is, either way, it will be used to screen rows after looking at them. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why can't I use an "AS" value in the WHERE clause.
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]
Re: Why can't I use an "AS" value in the WHERE clause.
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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why can't I use an "AS" value in the WHERE clause.
On Mon, Apr 05, 2004 at 08:03:33PM -0500, Paul DuBois wrote: > At 17:29 -0700 4/5/04, 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; > > I think you'll never be able to do it. > > The stuff after the SELECT is calculated based on the rows selected > by the WHERE. The WHERE therefore cannot be based on the stuff after > the SELECT. The parser has seen the 'as', though, and could expand it in the where clause so the user doesn't have to do it (and do it correctly, and maintain it in parallel). The problem is MySQL can't just start doing this without breaking queries that depend on it not happening. (If the 'as' alias is the same as a field name, for example.) So I doubt it will happen, but not because it couldn't be done. --Pete -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why can't I use an "AS" value in the WHERE clause.
Hi, This is what HAVING is for. :-) Matt - Original Message - From: "Joe Rhett" Sent: Monday, April 05, 2004 8:59 PM Subject: Re: Why can't I use an "AS" value in the WHERE clause. > On Mon, Apr 05, 2004 at 08:03:33PM -0500, Paul DuBois wrote: > > At 17:29 -0700 4/5/04, 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; > > > > I think you'll never be able to do it. > > > > The stuff after the SELECT is calculated based on the rows selected > > by the WHERE. The WHERE therefore cannot be based on the stuff after > > the SELECT. > > So why not the reverse? Allow aliasing in the WHERE clause, that we can > use in the select clause. Probably not ANSI, but MySQL isn't shy about that. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why can't I use an "AS" value in the WHERE clause.
On Mon, Apr 05, 2004 at 08:03:33PM -0500, Paul DuBois wrote: > At 17:29 -0700 4/5/04, 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; > > I think you'll never be able to do it. > > The stuff after the SELECT is calculated based on the rows selected > by the WHERE. The WHERE therefore cannot be based on the stuff after > the SELECT. So why not the reverse? Allow aliasing in the WHERE clause, that we can use in the select clause. Probably not ANSI, but MySQL isn't shy about that. -- Joe Rhett Chief Geek [EMAIL PROTECTED] Isite Services, Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why can't I use an "AS" value in the WHERE clause.
At 17:29 -0700 4/5/04, 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; I think you'll never be able to do it. The stuff after the SELECT is calculated based on the rows selected by the WHERE. The WHERE therefore cannot be based on the stuff after the SELECT. 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* -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Why can't I use an "AS" value in the WHERE clause.
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]
