CODEWITHSUNDEEP
mysql
ajdi
ajdi

Reputation: 454

mysql Select from where sum of columns is less than

I'm trying to select from a database table like the folowing...

id   | task         | last_run    | interval
 1   |  test-run    |  1369715091 |  30
 2   |  anothertask |  1369715091 |  60

last_run is unix-time, updated by the script, when it runs. Interval is the time between runs when this task needs to be executed.

What I need to do is run a query where I select only the tasks where last_run+interval is less than the current time.

This is what I've come up with...

SELECT *, (`last_run`+`interval`) as next_run FROM `tasks` WHERE (`next_run` > '1369717906')

...however I get the error...

Unknown column 'next_run' in 'where clause'

Do you have any ideas?

Upvotes: 0

Views: 1221

Answers (4)

alwaysLearn
alwaysLearn

Reputation: 6950

As far as i know where clause cant use alias

so you have two options

either repeat the expression in where clause 

SELECT *, (`last_run`+`interval`) as next_run FROM `tasks` WHERE (`last_run`+`interval`) > '1369717906'

Or use having which can use alias

SELECT *, (`last_run`+`interval`) as next_run FROM `tasks` HAVING (`next_run` > '1369717906')

see MySQL Tutorial and DEMO

Upvotes: 1

Hanky Panky
Hanky Panky

Reputation: 46900

You can provide those two names there instead of Alias

SELECT *, (`last_run`+`interval`) as next_run FROM `tasks` WHERE (`last_run`+`interval`)  > 1369717906

You cannot use Alias in a WHERE clause according to MySQL Docs

An alias can be used in a query select list to give a column a different name. You can use the alias in GROUP BY, ORDER BY, or HAVING clauses to refer to the column. Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined

Upvotes: 1

jspcal
jspcal

Reputation: 51904

In the where clause, use the expression rather than the alias:

WHERE last_run + interval > 1369717906

Upvotes: 1

sarwar026
sarwar026

Reputation: 3821

Use (last_run+interval) instead of next_run in your query.

Upvotes: 1

Related Questions