Row number regexp_split_to_table

Question

I have this request :

 select
    narttx,
    LIBETX
    --,row_number() over (partition by narttx)
from
(select
        codearticle_article as NARTTX,
        regexp_split_to_table(valeur_article_libelles, E'\x0b') as LIBETX
    from
        article.article a
    join article.article_libelles bl on
        bl.idarticle = a.idarticle
    and typelibelle_article_libelles = 'descriptifTech'
) vue
where   
narttx = '5627811' 
or 
narttx = '5627819'

It output this :

5627819 Finition
5627819 du
5627819 produit
...
5627811 Largeur (en cm) 
5627811 Hauteur (en cm)
5627811 Matière principale
...

I want to add the row number and reset it when the narttx change. So I uncomment the "--,row_number() over (partition by narttx)" part.

I should have :

5627819 Finition 1
5627819 du       2
5627819 produit  3
...
5627811 Largeur (en cm) 1
5627811 Hauteur (en cm) 2
5627811 Matière principale 3
...

But it don't work, the order is incorrect :

5627811 Largeur (en cm) 1
5627811 Hauteur (en cm) 2
5627811 Matière principale 3
...
5627819 Largeur 1
5627819 du  2
5627819 corps   3
5627819 de  4
5627819 meuble  5
...
5627819 Finition    110
5627819 de  111
5627819 prise   112
...

If I select just one article :

where   
narttx = '5627811'

It works well, but when I put two of them, the order is incorrect...

Why ?

Answer 1

Move regexp_split_to_array() to the from clause (where it belongs), then you can use with ordinality which will automatically return the array index:

select
    narttx,
    libetx, 
    rn
from (select
        codearticle_article as narttx,
        v.*
    from
        article.article a
    join article.article_libelles bl 
          on bl.idarticle = a.idarticle
      and typelibelle_article_libelles = 'descriptifTech'
      cross join regexp_split_to_table(valeur_article_libelles, E'\x0b') with ordinality as v(libetx, rn)
) vue
where   
narttx = '5627811' 
or 
narttx = '5627819'