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.

markovchain
  • 515
  • 1
  • 10
  • 25
  • which mysql version are you using? I can't reproduce... – user3459110 Jul 27 '14 at 06:01
  • Server version: 5.6.14 - MySQL Community Server (GPL). What result are you getting? – markovchain Jul 27 '14 at 06:06
  • alias should not be surrounded with double quote, use tick instead – Fabricator Jul 27 '14 at 06:09
  • tried this but no go :( – markovchain Jul 27 '14 at 06:13
  • Oh! Of course! It's actually a char type. ... But I just went ahead and corrected that and it still doesn't do what I want. It seems like the number value being compared to "indicator" doesnt matter, it's the sign. If I use "<=" it will give me 99, but if I use ">=" it will give me null – markovchain Jul 27 '14 at 06:28
  • 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) – Sami Korhonen Jul 27 '14 at 06:28
  • Tried using having but still having problems, I updated the OP – markovchain Jul 27 '14 at 06:47

6 Answers6

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.

Community
  • 1
  • 1
ltiong_sh
  • 3,186
  • 25
  • 28
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
Karolis
  • 9,396
  • 29
  • 38
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,

Arian Kiehr
  • 413
  • 5
  • 13
  • (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 – Arian Kiehr Jul 27 '14 at 06:27
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
Tom Lucas
  • 126
  • 1
  • 3
  • 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 – markovchain Jul 27 '14 at 06:40
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
LHristov
  • 1,103
  • 7
  • 16
  • remove the quotes from the alias... either leave the alias with no enclosing characters, or enclose it between backticks – Barranka Jul 27 '14 at 06:45
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`
)
markovchain
  • 515
  • 1
  • 10
  • 25
  • 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 – Justin Aug 27 '14 at 12:57