1

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.

Improve this question
7
  • which mysql version are you using? I can't reproduce... Commented Jul 27, 2014 at 6:01
  • Server version: 5.6.14 - MySQL Community Server (GPL). What result are you getting? Commented Jul 27, 2014 at 6:06
  • alias should not be surrounded with double quote, use tick instead Commented Jul 27, 2014 at 6:09
  • tried this but no go :( Commented Jul 27, 2014 at 6:13
  • 1
    Hmm, perhaps you should use peg_num >= 1 instead of idicator >= 1 (indicator is an alias formed using aggregate function max and it is not valid for where clause - you could use having as suggested earlier though) Commented Jul 27, 2014 at 6:28

6 Answers 6

Reset to default
1

  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
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

this gives me a 99 if I change >=1 to >=1000 or <=1 or <=1000 :(
Can you check the query twice? It's impossible to get 99 with the condition indicator >= 1000 :-)
1

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.

Improve this answer

Comments

0

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
Improve this answer

1 Comment

Not exactly. What I want it to do is to get the max value of peg_num (in this case, 99), but only if that max value is greater than some user input. But thanks for the info, I didn't know aliases couldn't be used in the WHERE clause up til now
0

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,

Improve this answer

1 Comment

(I was saying: WHERE don't know, i do) but you can't use aggregation functions in WHERE clause and i am sure that the order of execution is something like: FROM, WHERE, SELECT, HAVING (so you can't use AS in SELECT a then use it in WHERE. but you can use it in FROM). All this if we don't talk about sub-querys. If i am wrong let me know please, give some link
0
  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
Improve this answer

1 Comment

remove the quotes from the alias... either leave the alias with no enclosing characters, or enclose it between backticks
0

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` )
Improve this answer

1 Comment

you can do this without nested query: select case when MAX( peg_num )< 900 then MAX( peg_num ) else null end as key from list WHERE list_id =1

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.