SQL Query value divided by max (value)

Question

I am ashamed of myself because I cannot correctly execute this query ... I have a table like this

  nom  | code_geo | valeur |       indice
-------+----------+--------+--------------------
 AISNE | 02       |  81573 | 0.05
 SOMME | 80       |  79520 | 0.03
 OISE  | 60       |  70004 | 0.09

what I need to do is divide each "index" by max (index). I.e:

  nom  | code_geo | valeur |       indice
-------+----------+--------+--------------------
 AISNE | 02       |  81573 | 0.05 / 0.09
 SOMME | 80       |  79520 | 0.03 / 0.09
 OISE  | 60       |  70004 | 0.09 / 0.09

My first guess:

SELECT nom,code_geo,valeur,indice/(SELECT max(indice) FROM blablabla) FROM blablabla;

my problem is that "blablabla" is actually a function with 6 parameters, and I don't want to repeat the FROM clause in the subquery ...

Is there any way (better?) To do this? or should I look the other way.

thanks in advance

Answer 1

The solution looks great. Since the subquery does not correlate with the external query, the DBMS should evaluate this subquery only once.

Answer 2

, Postgresql CTE.

Select * into yourtable from blablabla


WITH T2(MaxNumber) as
(
select MAX(indice) as MaxNumber from yourtable
)
update t1
set t1.indice=t1.indice / T2.MaxNumber
from yourtable t1, T2