Discussion :

[Davboc]

Bonjour

J'ai une table avec 3 champs : id1(Number), id2(Number) et score(Number).

Pour un id1 donné, je voudrais garder uniquement les 5 lignes ayant le score le plus élevé, donc supprimer toutes les autres.

En théorie c'est tout bête, mais maintenant faut pondre la requête qui le fait et ça devient plus compliqué...

Je pense qu'il faut partir sur une requete utilisant une clause any comme cette requête que SQLPro a écrit pour supprimer les doublons d'une table :

Code :

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

1
2
3
4
5
6
 DELETE FROM Table t1
         WHERE t1.id1 < ANY ( SELECT t2.id1
                                            FROM Table t2
                                           WHERE t2.id1 <> t1.id1
                                               AND t2.champ = t1.champ
                                        );

Mais je ne vois pas vraiment comment faire... Quelqu'un se serait-il déjà penché sur la problématique ? Auriez-vous une idée sur la façon de procéder ?

Merci à vous

David

Edit : je suis sous Oracle 10gR2

[Loyd1974]

Code :

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

1
2
3
4
5
6
7
 
DELETE FROM TABLE
WHERE  Id1 IN (SELECT Id1
               FROM   (SELECT Id1,
                              Dense_Rank() Over(ORDER BY Score DESC) Dr
                       FROM   TABLE)
               WHERE  Dr > 5)

[Davboc]

wow...

Je connais pas tout ça ^^

Merci à toi en tout cas je vais tester !

[Loyd1974]

bon, allez plus simple pour changer

Code :

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

1
2
3
4
5
6
7
8
9
 
DELETE FROM TABLE
WHERE  Id1 IN (SELECT Id1
               FROM   (SELECT Id1,
                              Rownum AS Rn
                       FROM   (SELECT *
                               FROM   TABLE
                               ORDER  BY Score))
               WHERE  Rn > 5)

[Loyd1974]

D'ailleurs, attention, les 2 méthodes que je t'ai donné ne donne pas le même résultat.
Imaginons que tu ais le jeu de données suivant:

Code :

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

1
2
3
4
5
6
7
8
9
 
Id1 Score
1    10
2    11
3    12
4    13
5    14
6    15
7    12

Dans le premier cas avec le DENSE_RANK, tu vas supprimer uniquement que la ligne Id1 = 1
Dans le second cas, tu vas supprimer la ligne Id1 = 1 et Id1 = 2
En effet, avec DENSE_RANK, tu vas pouvoir trouver les 5 plus grandes valeurs et garder les enregistrements correspondants. Ces derniers pouvant être plus nombreux que 5 en cas d'égalité de score.
La fonctionnalité avec ROWNUM va te permettre de conserver uniquement 5 enregistrement, mais si ton plus grand score est par exemple 20, et que tu en as 6, tu vas n'en garder que 5, mais sans aucun contrôle sur lequel des 6 sera effacé.
Le problème avec Dense_Rank, c'est que si tu as 100 Id à 20, 50 Id à 19, 10 Id à 18, 10 à 17, 10 à 16, tu vas garder en tout (100+50+10+10+10) soit 180 enregistrements. Tu peux aussi utiliser Rank à la place de DENSE_RANK qui te permettra de ne garder que les 100 Id à 20
La commande devient simplement:

Code :

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

1
2
3
4
5
6
DELETE FROM TABLE
WHERE  Id1 IN (SELECT Id1
               FROM   (SELECT Id1,
                              Rank() Over(ORDER BY Score DESC) Dr
                       FROM   TABLE)
               WHERE  Dr > 5)

Le mieux est que tu joues chaque partie des sous selects pour que tu comprennes le fonctionnement de chacune

EDIT: Rectification de l'exemple avec RANK (j'avais laissé encore DENSE_RANK )

[Davboc]

Merci pour toutes ces explications

Je vais approfondir un peu la chose

[lallio]

En prenant le pb dans l'autre sens c'est d'utiliser la commande MERGE pour faire un insert si tu as moins de 5 lignes pour un id et un update du score le plus bas dans le cas contraire si ton score est > au min des 5 autres enregistrements.