1 as a number with SQL MAX query

[Emmanuelle_S]

my query works fine on any table
SELECT MAX(id_nombre) +1 AS max_id_nombre from {{table_bon_de_commande_client.data}}

NOT in one case: if the max value of my column is 1, it is considered as a text, and returns the value 11 for max(1)+1
Or for 0, if there is no value in my table, the result of this query is "undefined".

the exact same query works fine for all other values, if i add rows to my table. As you can see on the screenshots.

I have deleted and recreated my table, created new google sheet file and new queries, setting the good format for all the column.

Then, when i put decimals to my 1, the cell contains 1.0
the query returns max(1.0)+1 = 1.01

what can i do??? any idea? thank you very much

[Emmanuelle_S]

the problem is not the value 1,
the problem occurs when there is only one row of data in my table.
And i want to allow the creation of a new table from 0 row........

[Tess]

Hi @Emmanuelle_S

For the 1, I'm only able to reproduce this issue when 1 is a string. Even though your ID nombre column is a Number column, maybe the underlying data is a string? Changing the column type in the table will not impact anywhere that you're referencing table1.data

[Tess]

For 0, it looks like if there is a number 0 in your data, it should work. But if you're passing in an empty array for the table data, the expected result is undefined:

[Tess]

[Tess]

You could solve this with some JS that checks for an empty table & returns a 0 if needed

[Tess]

Let me know if you're still running into any issues with this information!

[Emmanuelle_S]

the topic was closed,
but just the 1 was considered as a string, not the other numbers so this worked:

SELECT
Case when MAX(id_nombre) > 1 then MAX(id_nombre)+1
ELSE 2
END
AS max_id_nombre
from {{table1.data}}

[Emmanuelle_S]

the solution :

select SUM(MAX(id_reporting),1) as new_line_reporting from {{ lire_ECONOMY.data }}

[kbn]

Thanks for circling back to share the solution, @Emmanuelle_S -- I merged your post into the original one so the solution is here on the thread.

We tend to close out threads that seem to have solutions marked/relevant advice after about 30 days of inactivity to keep the forum organized and searchable. Thanks again for taking the time to contribute the confirmed solution here in the forum!