Obtain the first free or id id in mysql

Navigation
7

I would like to know if there is any way to know the first free mysql record, but not the last, if not the first free that exists.

  

Ex:

     

I have a busy record 1,2,3,6,7,8,9   the first free would be 5

At the moment I have a code that tells me the last thing that would be this: 

$CIdmaxFact = "SELECT MAX(CAST(codfactura AS UNSIGNED)) AS ultimocod FROM Facturas WHERE empresa='".$_SESSION['Empresa_Id']."' AND serie='".$_POST["Infactura_serie"]."' "; //miramos de nuevo el ultimo codigo por si aca
        $RIdmaxFact = mysqli_query($Conectar ,$CIdmaxFact); //pongo un resultado de la consulta
        $DIdmaxFact=mysqli_fetch_array($RIdmaxFact); $idmaxFact = trim($DIdmaxFact["ultimocod"]); $idmaxFact ++;
        $idmaxFact = str_pad($idmaxFact, 4, "0", STR_PAD_LEFT);

But it does not help me at all, since only the last one tells me. Any ideas?

    
asked by Killpe 01.02.2017 в 20:37
source

2 answers

4

Someone asked a similar question here . And the accepted answer proposes some interesting solutions (I think w33haa was inspired from there), although I do not think they handle each case correctly. For example, it will not handle the case when the code 1 is free.

I propose the following adjusted solutions. To simplify the query, I'm going to assume that codfactura is numeric, although it does not seem to be true. You will have to add the casts where they are necessary: 

select codfactura + 1 as proximo_cod_libre
  from (select 0 as codfactura
         union all
        select codfactura
          from facturas
         where empresa = :empresa
           and serie = :serie) t1
 where not exists (select null
                     from facturas t2
                    where t2.codfactura = t1.codfactura + 1
                      and t2.empresa = :empresa
                      and t2.serie = :serie)
 order by codfactura
 limit 1

... or, using min() instead of order by ... limit 1 : 

select min(codfactura) + 1 as proximo_cod_libre
  from (select 0 as codfactura
         union all
        select codfactura
          from facturas
         where empresa = :empresa
           and serie = :serie) t1
 where not exists (select null
                     from facturas t2
                    where t2.codfactura = t1.codfactura + 1
                      and t2.empresa = :empresa
                      and t2.serie = :serie)
    
answered by 01.02.2017 / 21:15
source
8

You can use this little "trick" 

SELECT t.id +1
FROM Table1 t
WHERE NOT EXISTS (
    SELECT * 
    FROM Table1 t2
    WHERE t2.id = t.id + 1
)
LIMIT 1

I hope it serves you.

    
answered by 01.02.2017 в 21:06
Can more than one id be placed in a single input type text? Add 2 inputs side by side in the same column