Discussion :

[gaijinma]

Bonjour

Je suis novice en SQL et j'aimerai savoir s'il est possible de récupérer le max d'un row_number.

Je m'explique, j'ai une table sur laquelle j'ai effectué un row_number et j'aimerai récupérer la ligne avec le max du row_number.

Est-il possible de le faire en une seule requête ??

Mes deux prototypes de requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
with filtre1 as
(
  select row_number() OVER (PARTITION BY issuer_id ORDER BY issuer_id DESC) AS rk,
         t.*
    from mgu_sp_ei t
order by issuer_id asc
)
  ,  filtre2 as
(
  select max(rk) as rank, action, issuer_id, published_name, legal_name,
         short_name, sector, sub_sector, country, state, region, sic, gic,
         naics, naic, tsec, cusip, cins, ticker, reserved
    from filtre1
group by issuer_id, action, published_name, legal_name, short_name,
         sector, sub_sector, country, state, region, sic, gic, naics,
         naic, tsec, cusip, cins, ticker, reserved
)
select *
  from filtre2;

Pour ce prototype je me retrouve avec toutes les lignes alors que le but c'est la ligne avec le max du row_number.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
  select max(row_number() OVER (PARTITION BY issuer_id ORDER BY issuer_id DESC)) AS rk,
         t.*
    from mgu_sp_ei t
order by issuer_id asc;

erreur ORA-30483 : fonctions de fenêtrage interdites ici

Merci de votre aide.

[gaijinma]

Troisième prototype :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
with filtre1 as
(
  select row_number() OVER (PARTITION BY issuer_id ORDER BY issuer_id DESC) AS rk,
         t.*
    from mgu_sp_ei t
order by issuer_id asc       
)
  ,  filtre2 as
(
select max(rk) as rank,
       f.*
  from filtre1 f
)
select *
  from filtre2;

La encore erreur :

ORA-00937 : la fonction de groupe ne porte pas sur un groupe simple

[Waldar]

Si vous voulez récupérer la dernière ligne du row_number() trié dans un sens cela revient à récupérer la première ligne avec les données triées dans l'autre sens :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
WITH filtre1 AS
(
  SELECT row_number() OVER (PARTITION BY issuer_id ORDER BY issuer_id ASC) AS rk,
         t.*
    FROM mgu_sp_ei t
ORDER BY issuer_id ASC
)
select *
  from filtre1
 where rk = 1;

Si vous voulez savoir combien il y a de valeurs, utilisez count(*) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
  SELECT row_number() OVER (PARTITION BY issuer_id ORDER BY issuer_id DESC) AS rk,
         count(*) OVER (PARTITION BY issuer_id) as cnt,
         t.*
    FROM mgu_sp_ei t
ORDER BY issuer_id ASC;

Dans votre troisième requête, vous faites un MAX mais il n'y a pas de GROUP BY. C'est ce qu'indique le message d'erreur.

[gaijinma]

Bonjour

je vous remercie de votre réponse mais il se pose un petit problème : sur certains cas il ne me récupère pas la dernière ligne mais l'avant-dernière.

En fait pour la table mgu_sp_ei, elle est chargé a partir SQL*Loader.
Je peux avoir plusieurs lignes qui vont avoir les mêmes paramètres sauf au niveau d'un.

Le problème peut venir du fait qu'il va trier des fois par ordre alphabétique et ce qui fait que je ne récupère pas la bonne ligne, un exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
83	A	100016	African Development Bank	African Development Bank	AFDB	GLOBISS	FI	SUP		AUSNZ	9192	40101015	999010				V05973	1598Z	
84	A	100016	African Development Bank	African Development Bank	AFDB	GLOBISS	SOV	SUP		AUSNZ	9192	40101015	999010				V05973	1598Z

Dans cet exemple ce sont les deux dernières lignes de l'id 100016 sur les 84
et la il ne récupère pas la dernière mais l'avant-dernière en utilisant votre requête.

[Waldar]

Et bien rajoutez des éléments à vos ORDER BY pour les rendre déterministes en fonction de votre besoin !

[gaijinma]

C'est là où je bloque étant donné qu'en fonction des id les éléments vont changer.

Auriez-vous une idée ?

Merci encore.

[Waldar]

Essayez de mettre toutes les colonnes, vous allez voir ça va finir par marcher !

[gaijinma]

Je suis vraiment désolé de vous déranger encore une fois mais en essayant de mettre toutes les colonnes dans le ORDER BY ca ne change rien.

Il prend toujours l'avant dernière ligne, celle avec le FI au lieu de celle avec le SOV.

Le pire c'est que ça me bloque dans le suite de mes travaux.

[Waldar]

Je ne sais pas comment s'appelle la colonne où vous avez FI et SOV, disons col18.

Si actuellement vous avez cela :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

row_number() over(partition by... order by... , col18, col19...)

Mettez cela :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

row_number() over(partition by... order by... , col18 DESC, col19...)

C'est vous qui indiquez le tri à Oracle !

[gaijinma]

Ça marchera peut-être pour un id mais l'idée serait d'appliquer la méthode a tous les id.

C'est pour cela que j'avais pensé a passer par le MAX mais malheureusement il ne veut pas me sortir la ligne complète avec le max du row_number.

Je pensais que c'était la solution la plus simple, cf. mon deuxième prototype.

[Waldar]

Le max n'a pas de rapport avec la façon dont vous triez les données, si vous triez cinq lignes d'une façon ou d'une autre, le max vous donnera toujours cinq.

Si vous expliquiez clairement ce que vous voulez faire avec un petit jeu de test ?

[gaijinma]

Je vais essayer de l'expliquer de la manière la plus simple possible.

Je veux qu'il récupère la dernière ligne de chaque id :

Imaginons qu'un id se répète cinq fois dans une table, je veux qu'il numérote les lignes de 1 à 5 dans l'ordre dans lequel elles ont été chargées et qu'il récupère la cinquième ligne.

C'est aussi simple que ça.

[Waldar]

Et bien non ce n'est pas aussi simple.

Qu'est-ce qui défini l'ordre de chargement ?
Il n'y a pas d'ordre dans une table.

Vous pourriez par contre rajouter un id unique à l'aide d'une séquence au moment de charger les données, et là on a quelque chose qui permet de déterminer quelle est la "dernière" ligne chargée.
On pourrait utiliser des dates, mais si c'est un SQL*Loader, je ne pense pas que la précision soit suffisante. Peut-être avec un timestamp, mais essayez déjà la séquence.

[gaijinma]

Si j ai bien tout compris il serait préferable de mettre en place un numéro de ligne au moment du chargemenbt du fichier dans sql*loader

Par contre je ne sais pas du tout comment faire

Y'aurait il un poste au sein du forum vers lequel vous pourriez m'orienter

Merci encore de votre aide et surtout de votre patience

[Waldar]

Regardez ce lien :
http://oracle.developpez.com/faq/ind...#autoincrement