Discussion :

[tbc92]

J'ai une table avec une seule colonne COL01 utile pour ce besoin.
Je veux la 100ème valeur de cette colonne COL01.

J'ai cette requête, elle convient parfaitement :

Code :

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

1
2
3
select COL01 from 
( select COL01 , rank() over ( order by COL01) as rk  from MaTable )
where rk = 100

J'imagine qu'il y a une fonction analytique qui peut faire ça sans requête imbriquée mais je ne la trouve pas.
Ca c'est mon besoin d'aujourd'hui.

Mais j'imagine qu'il y a aussi une syntaxe pour réécrire la requête ci-dessous :

Code :

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

1
2
3
select   COL01, COL02, COL03 from 
( select  COL01, COL02 ,  COL03 rank() over ( partition by COL01  order by COL02) as rk  from MaTable )
where rk = 100

Autrement dit, dans chaque groupe identifié par COL01, récupérer la 100 ème plus petite valeur sur la colonne COL02, et récupérer la description COL03 correspondant à cette 100ème valeur.

Si au lieu de la 100ème ligne, je veux la 1ère ligne, je sais faire , avec MIN () KEEP (DENSE_RANK ...)
Mais pour une ligne arbitraire, autre que la 1ère ?

[vanagreg]

Bonjour,

Quelle est ta version de base? A partir de la version 11.2 tu as la fonction NTH_VALUE:

https://docs.oracle.com/cd/E11882_01...htm#SQLRF30031

[McM]

En 12c tu as le OFFSET : https://oracle-base.com/articles/12c...-queries-12cr1

Exemple pour avoir la 10ème ligne

Code :

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

1
2
3
4
5
WITH t AS (SELECT LEVEL num FROM dual connect BY LEVEL < 100)
   SELECT num
   FROM t
   ORDER BY num
   OFFSET 9 ROWS FETCH FIRST 1 ROW ONLY

10

La 10ème ligne en tri desc

Code :

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

1
2
3
4
5
   WITH t AS (SELECT LEVEL num FROM dual connect BY LEVEL < 100)
   SELECT num
   FROM t
   ORDER BY num desc
 OFFSET 9 ROWS FETCH FIRST 1 ROW ONLY

90


Pour ton second cas, je ne pense pas qu'une seule requête non imbriquée le fasse

[SQLpro]

En 12c tu as le OFFSET : https://oracle-base.com/articles/12c...-queries-12cr1

Exemple pour avoir la 10ème ligne

Code :

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

1
2
3
4
5
WITH t AS (SELECT LEVEL num FROM dual connect BY LEVEL < 100)
   SELECT num
   FROM t
   ORDER BY num
   OFFSET 9 ROWS FETCH FIRST 1 ROW ONLY

10

ATTENTION : ceci peut être faux.... 100e valeur ne veut pas dire 100e ligne... À cause des ex æquo ! Une requête avec un RANK est donc nécessaire pour ce faire...

A +

[vanagreg]

ATTENTION : ceci peut être faux.... 100e valeur ne veut pas dire 100e ligne... À cause des ex æquo !

Dans ce cas en effet l'utilisation de RANK() pour faire rnk = 100 peut ne rien ramener même s'il y a plus de 100 lignes. Il faut alors utiliser DENSE_RANK(), mais on peut ramener plusieurs lignes. Le mieux c'est ROW_NUMER() dans ce cas, d'ailleurs sous le capot la ROW LIMITING CLAUSE d'Oracle utilise ROW_NUMBER().

[tbc92]

Je suis en version 12C.

J'avais regardé nth_value() avant de poser la question, et a priori, ça ne convient pas. Mais je vais fouiller plus, parce que je ne comprends pas à 100% ce que fait cette fonction.

Offset convient parfaitement. La gestion des ex-aequo me convient. Si mes valeurs sont 1,2,2,3,3,3,4,4 4, 5,5,5 etc. , je considère que la 10ème valeur de cette liste est 5. Et de toutes façons, dans ma clause de tri, je fais en sorte de ne pas avoir d'ex-aequo.

Merci.

[vanagreg]

Bonjour,

nth_value ne conviendra pas en effet puisque tu veux une seule ligne. Puisque tu es en 12c tu peux utiliser une requête match_recognize pour ton second point:

Code :

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

1
2
3
4
5
6
7
8
9
10
select * from matable
 match_recognize(partition by col01
                 order by col02
                 measures match_number() as mn,
                          col02 as col02,
                          col03 as col03
                 pattern (ligne{1}) 
                 define ligne as 1=1          
                )
where mn = 100;

[JeitEmgie]

Testé différentes versions avec match_recognize (notamment pattern A{1} et A{100}) sur une table de > 6M objects :
nécessite plus ou moins 60% du temps de la version avec rank() et pas de différences significatives entre les différents pattern.