MYSQL ROW_NUMBER

Question

I'm using row_number function to rank by partition but I'm trying to use the result of row_number in a IF statement without success.

My code:

ROW_NUMBER() OVER (partition by wboi.ce19.store, dbn.ce19.COD, dbn.ce19.MOD ORDER by dbn.ce19.store, dbn.ce19.data, dbn.ce01.COD) AS ROW1,
IF('ROW1' = 1, 0, 1) as TEST

But all the values in "TEST" column are getting 1, even if the row_number is 1.

Also, I tried to make sum with the Row_number() like:

'ROW1' + 1 AS TEST2

But all the values is 1.

So, ROW_NUMBER() function output is a number or I need to convert it to number before use it in any conditional function?

Answer 1

'ROW1' is not the column ROW1, it's a string literal.

The single-quotes are used to delimit strings. To delimit identifiers, use back-ticks.

Using a string literal in a numeric context converts the string to its integer value, which is the leading digits (if any) or else 0.

So 'ROW1' + 1 is equal to 0 + 1 or 1.

Besides that, you cannot make an SQL query that references a column alias in the same select-list. In other words, this is an error:

mysql> SELECT 1 AS col1, col1+1 AS col2;
ERROR 1054 (42S22): Unknown column 'col1' in 'field list'

You would have to wrap it in a subquery or a CTE:

mysql> SELECT col1+1 AS col2 FROM (SELECT 1 AS col1) AS t;
+------+
| col2 |
+------+
|    2 |
+------+