Discussion :

[Negaton]

Bonjour,
Y'a t-il un moyen en sql de déterminer le premier entier non utilisé dans la liste des occurences d'une table.
Par exemple, si j'ai une table avec un champ "code" de type entier et les occurences : "1,2,3,5,6", y'at-il un moyen, sans parcourir chaque occurence de la table, de retourner "4"

Merci

[Waldar]

Pouvez-vous préciser votre besoin fonctionnel associé ?

[Negaton]

Bien sur.
Le champ entier en question est une clé, de type entier, et je ne peux malheureusement pas passer ce champ en "compteur" (structure de la table synchronisée sur des périphériques mobiles en production)
Par contre, un intranet peut ajouter et supprimer des occurences dans cette table, et je ne veux pas laisser de "trous" non utilisés après des suppressions.

J'utilise sql server. Je peux faire un parcours de curseur "FETCH CURSOR" et m'arréter au premier entier disponible, mais cette solution me parait horrible.

En espérant être clair

Merci pour votre aide

[SQLpro]

C'est tout à fait possible à l'aide des CTE. Démonstration

Voici le jeu de table de départ :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE DATABASE DB_TEST;
GO
 
USE DB_TEST;
GO
 
CREATE TABLE INC (N INT);
GO
 
INSERT INTO INC VALUES (1);
INSERT INTO INC VALUES (2);
INSERT INTO INC VALUES (3);
INSERT INTO INC VALUES (5);
INSERT INTO INC VALUES (6);

Voici maintenant la requête qui trouve le premier trou :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
WITH
T AS (SELECT MIN(N) AS I, MAX(N) AS M
      FROM   INC
      UNION ALL
      SELECT I + 1, M
      FROM   T
      WHERE  I <= M),
U AS (SELECT I
      FROM   T
      EXCEPT
      SELECT N
      FROM   INC)
SELECT MIN(I) AS I
FROM   U;

et le résultat :

Code :

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

1
2
3
I
-----------
4

Bien entendu ce n'est pas la solution la plus performante. La plus performante consiste à ajouter à votre base une table des nombres et de faire une requête du même genre que la finale dessus.

A +

[Negaton]

Super ce code, je le garde de coté !

Malheureusement, j'ai oublié de préciser que j'étais en Sql Server 2000.
Et, sauf erreur de ma part, je n'ai pas droit aux CTE.

J'ai pensé à l'idée de créer une table des nombres mais le modèle de données (structure et nombre des tables) est figé et demande un protocole de mise à jour important et contraignant si on le modifie

[Waldar]

En SQL Server 2000 la requête suivante doit fonctionner.
L'idée est de comparer votre ID avec ID+1 et de vérifier sa non-existence.

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
select min(T.nb+1) AS nb_dispo
from 
(
select 1 nb union all
select 2 union all
select 3 union all
select 6 union all
select 7 union all
select 9
) AS T
where not exists (select *
                  from (select 1 nb union all
                        select 2 union all
                        select 3 union all
                        select 6 union all
                        select 7 union all
                        select 9
                        ) AS U
                   where T.nb+1 = U.nb)

Code :

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

1
2
3
4
5
nb_dispo
-----------
4
 
(1 row(s) affected)

Attention ça reste très limité dans son utilisation.

[Negaton]

Merci pour votre réponse.
J'ai actuellement près de 3000 occurences dans la table en question.
Ne sachant pas à quel moment je vais avoir le premier entier dispo (se peut être 4 comme 2500), je ne peux pas envisager ce genre de recherche.

Pour tout dire, j'avais envisagé quelque chose du genre avec une table temporaire alimentée de 1 à max(code)+1 (ex. si ma table contient au maximum le code 3628, je crée une table temporaire avec un champ entier unique dont les occurences vont de 1 à 3629).
Ensuite, je pensais faire un "not in" entre les deux tables.

Mais j'ai malgré tout une boucle (ici pour l'alimentation de la table temporaire) et je souhaiterais éviter celà, si possible.

[oOVaveOo]

Bonsoir,
Une pirouette de ce genre ne pourrait pas marcher ?

Code :

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

1
2
3
4
5
select (min(T1.id2) - 1) from
          (select T2.id as id2 from table as T2
          where (T2.id - 1) not in (select T3.id from table as T3) 
          and T2.id <> 1)
          as T1

[Waldar]

Merci pour votre réponse.
J'ai actuellement près de 3000 occurences dans la table en question.
Ne sachant pas à quel moment je vais avoir le premier entier dispo (se peut être 4 comme 2500), je ne peux pas envisager ce genre de recherche.

Je viens de lancer cette requête sur une table de 31.000 lignes, le champ d'identification est indexé.
Le premier trou était situé dans les 8000 et j'ai obtenu le résultat en 32 ms.
Aucun problème de performance donc.

Qu'est-ce qui vous pose problème avec la requête que je vous ai suggéré ? Elle n'est pas itérative.

[SQLpro]

Une autre façon de voir la chose, si vous êtes sûr d'avoir un trou est la suivante :

Code :

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

1
2
3
4
5
6
SELECT N1.N+1
FROM   INC AS N1
       JOIN INC AS N2
            ON N1.N < N2.N
GROUP  BY N1.N
HAVING N1.N < MIN(N2.N) - 1

Si vous n'êtes pas sur vous pouvez ajouter en UNION ALL la requête suivante :

Code :

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

1
2
SELECT MAX(N) + 1
FROM   INC

Pour combiner le tout de la sorte :

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT MIN(NEW)
FROM   (SELECT N1.N+1 AS NEW
        FROM   INC AS N1
               JOIN INC AS N2
                    ON N1.N < N2.N
        GROUP  BY N1.N
        HAVING N1.N < MIN(N2.N) - 1 
        UNION ALL
        SELECT MAX(N) + 1
        FROM   INC) AS T

A +

[pacmann]

Salut !

Si tu veux économiser une jointure (à priori, la méthode ne devrait pas nécessiter plus d'un tri), et surtout si ton SGBD connaît les fonctions analytiques :

- Si tu as droit à la fonction LEAD, c'est tout simple : la fonction te permet de regarder la prochaine valeur en fonction d'un certain critère de tri.
- Sinon, tu peux simplement calculer la différence entre le nombre et son rang dans le tri des lignes.

Création, remplissage de la table (juste pour se marrer... Oui je sais, c'est du spécifique Oracle !)

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
 
CREATE TABLE INC 
(N INT)
;
 
INSERT INTO INC(N)
SELECT * 
FROM (
SELECT level
FROM dual
CONNECT BY 1 = 1) t
WHERE rownum < 100
 
DELETE FROM INC
WHERE N IN (1, 51)
commit

Première version :

Code :

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

1
2
3
4
5
6
 
SELECT min(N)
FROM (
select N, lead(N, 1) over (order by N) as nxt
from inc ) t
WHERE nxt - N > 1

Deuxième version :

Code :

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

1
2
3
4
5
6
7
8
 
SELECT min(N) 
FROM (
select N,  
N - min(N) over(partition by NULL) + 1 - row_number() over(order by N) decal
from inc
) 
WHERE decal = 1

[SQLpro]

LEAD n'est actuellement implémenté que sur Oracle et peut être Sybase (à vérifier). Cela fait en effet partie de la norme SQL 2008, pas encore très implantée !

De plus l'horrible CONNECT BY est strictement spécifique à Oracle et a été refusé à bon escient par le comité de normalisation qui à préféré les CTE pour la gestion des requêtes récursives !
A lire : http://sqlpro.developpez.com/cours/s...te-recursives/

A +

[pacmann]

LEAD n'est actuellement implémenté que sur Oracle et peut être Sybase (à vérifier). Cela fait en effet partie de la norme SQL 2008, pas encore très implantée !

De plus l'horrible CONNECT BY est strictement spécifique à Oracle et a été refusé à bon escient par le comité de normalisation qui à préféré les CTE pour la gestion des requêtes récursives !
A lire : http://sqlpro.developpez.com/cours/s...te-recursives/

A +

Merci pour la précision sur la norme, SQLPro.
Effectivement, même sous Oracle la fonction n'est disponible qu'à partir de 10g... et c'est pour ça que je proposais également la deuxième version.

Concernant CONNECT BY, c'était plus pour rigoler parce que je trouvais amusant de générer une suite d'entiers avec LEVEL.
CONNECT BY n'est pas dans la norme, mais malheuresement Oracle n'implémente pas l'utilisation des CTE pour les requêtes récursives...
Et de plus, ROWNUM est également une spécificité Oracle.
Cela dit, promis : j'arrête de faire des blagues !

[Waldar]

LEAD n'est actuellement implémenté que sur Oracle et peut être Sybase (à vérifier). Cela fait en effet partie de la norme SQL 2008, pas encore très implantée !

De plus l'horrible CONNECT BY est strictement spécifique à Oracle et a été refusé à bon escient par le comité de normalisation qui à préféré les CTE pour la gestion des requêtes récursives !
A lire : http://sqlpro.developpez.com/cours/s...te-recursives/

A +

C'est amusant dans le forum Oracle nous évoquions ce sujet dans ce fil :
http://www.developpez.net/forums/d67...s-circulaires/

[Snipah]

Salut,

Une petite précision concernant les requetes de Pacmann :

Première version :

Code :

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

1
2
3
4
5
6
 
SELECT min(N)
FROM (
select N, lead(N, 1) over (order by N) as nxt
from inc ) t
WHERE nxt - N > 1

Deuxième version :

Code :

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

1
2
3
4
5
6
7
8
 
SELECT min(N) 
FROM (
select N,  
N - min(N) over(partition by NULL) + 1 - row_number() over(order by N) decal
from inc
) 
WHERE decal = 1

A moins que quelque chose m'echappe, la première version a besoin d'un +1, la seconde d'un -1 voir plus.

Dans la seconde version, si on a un trou plus grand que 1, decal ne sera pas égal à 1 mais supérieur à 1. On peut donc modifier la clause decal = 1 par decal >= 1, toujours est-il que la requete ne renverra pas le premier entier disponible, mais le dernier du premier trou, ce qui ne semble pas très adapté au besoin, bien que la requete soit jolie
Ca oblige à retrancher decal du resultat, donc d'ajouter une étape dans la requete. Ya peut être plus simple, j'ai pas trop creusé.

Aussi, les requetes renvoient NULL si il n'y a pas de trou dans la suite de nombre, or j'imagine que dans ce cas le premier entier dispo est le max de la séquence + 1, il faudrait donc ajouter une UNION pour gérer ce cas.
Ce qui donnerait :

Première version :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
SELECT min(N) + 1 as dispo
FROM 
(
	SELECT N, lead(N, 1) over (ORDER BY N) AS nxt
	FROM inc 
	UNION ALL
	SELECT max(N), max(N)+2
	FROM inc
) t
WHERE nxt - N > 1

Deuxième version :

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
 
SELECT min(N) as dispo
FROM 
(
 SELECT N - decal as N
 FROM 
 (
 	  SELECT N, 
 		 N - min(N) over (partition BY NULL) + 1 - row_number() over(ORDER BY N) decal
 	  FROM inc
 	  UNION ALL
 	  SELECT max(N)+2, 1
 	  FROM inc
 ) 
 WHERE decal >= 1
)

A voir aussi si on doit considérer 1 comme dispo si la suite de nombre ne commence pas par 1.

[Negaton]

Je viens de lancer cette requête sur une table de 31.000 lignes, le champ d'identification est indexé.
Le premier trou était situé dans les 8000 et j'ai obtenu le résultat en 32 ms.
Aucun problème de performance donc.

Qu'est-ce qui vous pose problème avec la requête que je vous ai suggéré ? Elle n'est pas itérative.

Bien alors désolé mais je n'ai pas du comprendre la requête : les select 1, select 2 ... me laissait à penser qu'il fallait lister tous les entiers de 1 jusqu'au max de ma table.
Si ce n'est pas le cas, je n'ai pas compris la requête

[Negaton]

Une autre façon de voir la chose, si vous êtes sûr d'avoir un trou est la suivante :

Code :

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

1
2
3
4
5
6
SELECT N1.N+1
FROM   INC AS N1
       JOIN INC AS N2
            ON N1.N < N2.N
GROUP  BY N1.N
HAVING N1.N < MIN(N2.N) - 1

Si vous n'êtes pas sur vous pouvez ajouter en UNION ALL la requête suivante :

Code :

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

1
2
SELECT MAX(N) + 1
FROM   INC

Pour combiner le tout de la sorte :

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT MIN(NEW)
FROM   (SELECT N1.N+1 AS NEW
        FROM   INC AS N1
               JOIN INC AS N2
                    ON N1.N < N2.N
        GROUP  BY N1.N
        HAVING N1.N < MIN(N2.N) - 1 
        UNION ALL
        SELECT MAX(N) + 1
        FROM   INC) AS T

A +

Et bien c'est exactement ça. J'ai des progrès à faire en sql !


J'ai rajouté un autre union all, pour gérer le cas ou un premier trou est en début de fichier :
par exemple, si j'ai la suite "2,3,4,5" je souhaite retourner "1" et votre requête retourne "6".

voici donc ma modification (minime, le plus dur étant fait par votre requete)
qui intègre un troisième union all

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
 
SELECT MIN(New)
FROM   (SELECT N1.N+1  as New
        FROM   INC AS N1
               JOIN INC AS N2
                    ON N1.N < N2.N
        GROUP  BY N1.N
        HAVING N1.N < MIN(N2.N) - 1 
	UNION ALL
	SELECT min(N3.N)-1 As New
	FROM INC as N3
	WHERE N3.N>=0 
	HAVING Min(N3.N) NOT IN (SELECT N4.N FROM INC N4)
        UNION ALL
        SELECT MAX(N) + 1
        FROM   INC) AS T

Merci beaucoup SQLpro

[Waldar]

Bien alors désolé mais je n'ai pas du comprendre la requête : les select 1, select 2 ... me laissait à penser qu'il fallait lister tous les entiers de 1 jusqu'au max de ma table.
Si ce n'est pas le cas, je n'ai pas compris la requête

Les select 1, select 2 etc. sont là pour simuler une table et faire un jeu de test, la table INC de SQLPro si vous préférez :

Code :

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

1
2
3
4
5
SELECT min(T.nb+1) AS nb_dispo
FROM INC AS T
WHERE NOT EXISTS (SELECT *
                  FROM INC AS U
                   WHERE T.nb+1 = U.nb)

Dommage que vous n'exécutiez pas tout ce qui est proposé, vous savez quand on réfléchi pour vous sur un problème ça me paraît être un minimum de faire un copier coller exécuter. Bref.

[Negaton]

Dommage que vous n'exécutiez pas tout ce qui est proposé, vous savez quand on réfléchi pour vous sur un problème ça me paraît être un minimum de faire un copier coller exécuter. Bref.

Je suis désolé, vous avez raison.
J'ai lu trop vite votre réponse, ayant envisagé de faire un "not exist" depuis une table remplie de façon ittérative, j'ai cru que c'était ce que vous me proposiez.

Mea culpa.

Ainsi, et pour la même raison que sqlpro, voici votre code, qui fonctionne aussi très bien, amélioré afin de proposer, sur la liste "3,5,6" la valeur "2" et non automatiquement la valeur "7"

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
 
select min(nb_dispo) as nb_dispo
FROM
(
SELECT min(T.N+1) AS nb_dispo
FROM INC AS T
WHERE NOT EXISTS (SELECT *
                  FROM INC AS U
                   WHERE T.N+1 = U.N)
UNION ALL
SELECT min(T1.N-1) as nb_dispo
FROM INC as T1
WHERE NOT EXISTS (SELECT *
		FROM INC as U1
		WHERE T1.N-1=U1.N
		)
HAVING min(T1.N-1)>0
) as T

[Negaton]

Pour résumer, si vous avez ce problème, les deux solutions ci-dessus (Waldar et Sqlpro) fonctionnent à merveille
Merci à tous pour votre aide