CODEWITHSUNDEEP
mysql
markovchain
markovchain

Reputation: 503

Use the result of the MySQL SELECT query as a WHERE condition in the same query

I'm trying to do this query:

SELECT MAX(`peg_num`)
  AS "indicator"
FROM `list`
WHERE `list_id` = 1
  AND "indicator" >= 1

But I'm getting the result of NULL. What I should be getting is 99, as the range of peg_num is 00 to 99.

The value checked against "indicator" should actually be a user input, so I want it to be versatile. But, it does give me the correct result if I flip the equality around:

SELECT MAX(`peg_num`)
  AS "indicator"
FROM `list`
WHERE `list_id` = 1
  AND "indicator" <= 1

Why would it do this?

Edit:

As suggested, I'm using the HAVING clause... but I just ditched the alias for now anyway:

SELECT MAX(`peg_num`) AS "indicator"
FROM `list`
GROUP BY `list_id`
HAVING MAX(`peg_num`) <= 40

Still very stubborn. It gives me 99 now no matter the value in the having clause, regardless of the inequality.

Edit2:

As a clarification:

What I want to happen is the query select the largest value in the range of peg_num, but only if it is larger than a user-given input. So, the max in this case is 99. If the user wants to select a number like 101, he/she can't because it's not in the range.

Upvotes: 1

Views: 998

Answers (6)

markovchain
markovchain

Reputation: 503

I completely re-invented my query and it worked. The thing is, I had to use a nested query (and I wanted to not do that as much as possible, my professor had always discouraged it).

Anyway, here it is:

SELECT IF(`key` < 900, `key`, null) `key`
FROM (
(

    SELECT MAX(  `peg_num` ) AS  `key` 
    FROM  `list` 
    WHERE  `list_id` =1
    ) AS  `derivedTable`
)

Upvotes: 0

LHristov
LHristov

Reputation: 1123

  1. You should use HAVING
  2. No quotes in HAVING condition

This must work:

SELECT MAX(peg_num)
 AS indicator
FROM list
WHERE list_id = 1
  HAVING indicator >= 1

Upvotes: 0

ltiong_dbl
ltiong_dbl

Reputation: 3216

You might want to check out the link on the answer to another Stack question about not being allowed to use alias in where clause:

Can you use an alias in the WHERE clause in mysql?

Paul Dixon cites:

It is not allowable to refer to a column alias in a WHERE clause, because the column value might not yet be determined when the WHERE clause is executed. See Section B.1.5.4, “Problems with Column Aliases”.

Also:

Standard SQL disallows references to column aliases in a WHERE clause.

The behavior you're seeing in your query when you swap the '<=' and '>=' operators, results from the query comparing the string/varchar 'indicator' to the number 1.

That's why you see the correct answer..when ('indicator' >= 1) which is true, and null when ('indicator' <= 1) which is false.

Upvotes: 1

Karolis
Karolis

Reputation: 9562

  1. Because of double quotes, "indicator" in WHERE clause is interpreted as a string. Thus, it evaluates to 0, meaning it is always less than 1. Column names must be escaped in backticks.

  2. Keep in mind that WHERE clause is executed before SELECT an hence aliases defined in SELECT can not be used in WHERE clause.

    SELECT MAX(`peg_num`) AS `indicator`
FROM `list`
WHERE `list_id` = 1
HAVING `indicator` >= 1

Upvotes: 1

Arian Kiehr
Arian Kiehr

Reputation: 451

WHERE happens before SELECT, and don't know what's "indicator".

You should use HAVING (with GROUP BY) to use the SELECT fields

Here's the documentation for syntax

http://dev.mysql.com/doc/refman/5.5/en/select.html

Something like this is the idea

SELECT MAX(peg_num) AS indicator
FROM list
WHERE list_id = 1
HAVING indicator <= 1

I can't test it and i never met Mysql so just the idea,

Upvotes: 0

Tom Lucas
Tom Lucas

Reputation: 146

I don't know, but I'm amazed either of them work at all. WHERE works serially on fields belonging to individual records and I wouldn't expect it to work on "indicator" since that's a group calculation.

Does this do what you want?

SELECT max(`peg_num` ) AS "indicator"
FROM actions
WHERE `peg_num` >=1
AND `list_id` <= 1

Upvotes: 0

Related Questions