numéroter des lignes selon les valeurs d'une colonne [Résolu]

[mitmit]

Bonjour à tous

On vient de me soumettre un problème et je m'interroge sur la possibilité de le résoudre en SQL (sans utiliser de PL/SQL).

Il s'agit de faire un SELECT sur une table dont le contenu ressemble à ça :

Code :

1
2
3
4
5
6
7
col1 | col2 | col3
  1     a       c   
  2     b       d
  2     b       e
  3     a       c
  3     b       d
  3     b       e

L'idée est d'obtenir une 4ème colonne comportant le numéro de l'occurence de la colonne col1 :

Code :

1
2
3
4
5
6
7
col1 | col2 | col3 | col4
  1     a       c     1      
  2     b       d     1
  2     b       e     2
  3     a       c     1
  3     b       d     2 
  3     b       e     3

Pour le moment je fais quelques essais en SQL pour essayer de compter le nombre d'occurences de chaque valeur de col1, mais c'est assez peu concluant :

Code :

1
2
3
SELECT a.col1,a.col2,a.col3,count(*) AS col4
FROM TABLE a
GROUP BY a.col1,a.col2,a.col3

Cette requête met col4 à 1 systématiquement, puisque le group by contient tous les champs (passage obligé puisque j'ai besoin de ces champs dans le select) et que chaque combinaison des trois champs n'a qu'une seule occurence...

Je continue à fouiller, mais si quelqu'un à une idée (un genre de rownum par valeurs d'une colonne peut-être)...

Merci d'avance

[jkofr]

Hello,

Fonction: dense_rank

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
 
SELECT   Col1,col2,col3, 
         dense_rank() over(ORDER BY Col1) Num_occ
FROM
   (
	WITH Tab AS
	(
	SELECT 1        AS col1     , 'a'     AS col2     , 'c'    AS col3  FROM dual 
	union ALL
	SELECT 2        AS col1     , 'b'     AS col2     , 'd'    AS col3  FROM dual 
	union ALL
	SELECT 2        AS col1     , 'b'     AS col2     , 'e'    AS col3  FROM dual 
	union ALL
	SELECT 3        AS col1     , 'a'     AS col2     , 'c'    AS col3  FROM dual 
	union ALL
	SELECT 3        AS col1     , 'b'    AS col2     ,  'd'    AS col3  FROM dual 
	union ALL
	SELECT 3        AS col1     , 'b'     AS col2     , 'e'    AS col3  FROM dual 
	)
	SELECT * FROM tab   
   )

Avec des lettres:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
 
SELECT   Col1,col2,col3, 
         dense_rank() over(ORDER BY Col1) Num_occ
FROM
   (
	WITH Tab AS
	(
	SELECT 'a'        AS col1     , 'a'     AS col2     , 'c'    AS col3  FROM dual 
	union ALL
	SELECT 'g'        AS col1     , 'b'     AS col2     , 'd'    AS col3  FROM dual 
	union ALL
	SELECT 'c'        AS col1     , 'b'     AS col2     , 'e'    AS col3  FROM dual 
	union ALL
	SELECT 'a'        AS col1     , 'a'     AS col2     , 'c'    AS col3  FROM dual 
	union ALL
	SELECT 't'        AS col1     , 'b'    AS col2     ,  'd'    AS col3  FROM dual 
	union ALL
	SELECT 'e'        AS col1     , 'b'     AS col2     , 'e'    AS col3  FROM dual 
	)
	SELECT * FROM tab   
   )
 
COL1 COL2 COL3 NUM_OCC                
---- ---- ---- ---------------------- 
a    a    c    1                      
a    a    c    1                      
c    b    e    2                      
e    b    e    3                      
g    b    d    4                      
t    b    d    5

jko

[Yanika_bzh]

Ce genre de requete peut vous aider :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
SELECT 
	A.Col1,
	A.Col2,
	A.Col3,
	COUNT(1)
FROM
	MaTable A INNER JOIN MaTable B 
	ON (A.Col1 = B.col1  AND A.Col3<=B.Col3)
GROUP BY
	A.Col1,
	A.Col2,
	A.Col3

A tester et adapter

Bon courage

[mitmit]

@jkofr

avant tout merci pour ta réponse ultra rapide

par contre ce que tu me proposes ne correspond pas exactement à ce qu'il me faut...

avec ton exemple, voilà ce que je souhaiterai obtenir :

Code :

1
2
3
4
5
6
7
8
9
COL1 COL2 COL3 NUM_OCC                
---- ---- ---- ---------------------- 
a    a    c    1                      
a    a    c    2                    
c    b    e    1                      
e    b    e    1                      
g    b    d    1                      
g    b    e    2
g    c    d    3

@yanika

cela ressemble effectivement aux tests que je suis en train de faire... merci pour ton aide

[EDIT] : j'ai ma solution

Code :

1
2
SELECT col1,col2,col3,RANK() OVER (PARTITION BY col1 ORDER BY col3) AS col4
FROM (SELECT * FROM tab)

Avec ça j'obtiens bien le numéro de l'occurence de col1 pour chaque valeur de col1.
Merci pour m'avoir mis sur la piste !

Bonne journée à tous

[jkofr]

Oui, c juste.
J'avais pas compris ce point la.

Ravis que tu ais trouve ta solution.

jko