1

I would like to know if it's possible to find a value in a select statement and use it in a where clause like:

SELECT col1, MAX(col2) - COUNT(DISTINCT col3) as variable
FROM table
WHERE col1 > variable

"table" is pretty big, and I want to narrow down the records the query has to look at as quickly as possible.

I know this isn't bit of code isn't possible as written (#1054 - Unknown column 'variable' in 'where clause'), but is there anyway to figure out the value of "variable" and then use it in the WHERE clause?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Troy Knapp
  • 517
  • 3
  • 8
  • 17

2 Answers2

1

You could try subquery syntax, also called nested select.

I think something like:

SELECT col1 WHERE col1 > (SELECT MAX(col2) - COUNT(DISTINCT col3))

See the MySQL manual for some better examples.

danielgwood
  • 212
  • 1
  • 9
0

In some cases you can replicate an expression in a WHERE clause, as demonstrated by RedFilter. In other cases, this isn't possible and you can use a HAVING clause, e.g.

SELECT col1, MAX(col2) - COUNT(DISTINCT col3) as variable
FROM table
HAVING col1 > variable

HAVING is less efficient, think of it as a resultset post-processor rather than something the query optimizer can do anything with.

Paul Dixon
  • 295,876
  • 54
  • 310
  • 348