Find minimum non-used value from collection of rows marked with an id and customId

Question

I already saw a similar question here(Get minimum unused value in MySQL column) which is exactly what I want except what I need to select the minimum available number not from just a table but from rows with specific customId as well. In case the other question gets removed or something, here is what query is needed:

In case of rows [1,2,3] the query should return 4.

In case of rows [2,3,4] the query should return 1.

In case of rows [1,3,4] the query should return 2.

In case of multiple missing rows [1,2,4,6,7] the query should return minimum missing value 3.

I tried solutions showed in the first linked question and from this one as well link(SQL - Find the lowest unused number). I tried tweaking them to include customId in WHERE clauses but the queries is too advanced and confusing for me, so it did not work. I have tried doing this:

  SELECT min(unused) AS unused
  FROM (
  SELECT MIN(t1.id)+1 as unused
  FROM yourTable AS t1
  WHERE t1.customId = ? AND NOT EXISTS (SELECT * FROM yourTable AS t2 WHERE t2.customId = ? 
   AND t2.id = t1.id+1)
  UNION
  -- Special case for missing the first row
  SELECT 1
  FROM DUAL
    WHERE customId = ? AND NOT EXISTS (SELECT * FROM yourTable WHERE id = 1)

  )AS subquery

But it shows access or syntax violation error.

Mooneye

Where is the customId column in your examples? You don't show anything different from the previous questions. – Rosarosabel

You say you tried tweaking the other answers to do what you want. Show what you tried. – Rosarosabel

@barmar I described the role of customId before showing the examples. I will update for some examples of what I have attempted – Mooneye

More details are needed. What if there's a gap in the ID sequence with the custom ID, but it's filled in with a row that doesn't have that custom ID? Show example data that has both columns, and the desired result. – Rosarosabel

I don't get a syntax error when I replace ? with an ID number. When you have a placeholder in the query, you have to prepare it, not use it directly. – Rosarosabel

Answer 1

I tweaked one of the queries I found on the web until it worked... Obviously it is not neccesarily fast or perfomant but it work, so here it goes:

SELECT min(unused) AS unused FROM
  ( SELECT MIN(t1.group_number)+1 as unused FROM units AS t1 WHERE t1.user_id = '.$ai_id.' AND 
     NOT EXISTS (SELECT * FROM units AS t2 WHERE t2.user_id = '.$ai_id.' AND t2.group_number= 
   t1.group_number +1) UNION
    -- Special case for missing the first row 
    SELECT 1 FROM DUAL WHERE NOT EXISTS (SELECT * FROM units  WHERE  group_number= 1 
    AND user_id = '.$ai_id.') )AS subquery

I am not sure how exactly it works, but it somehow does, I can only get the outlines... The user_id in this case is the aforementioned customId and unit_group_number is the column used to search for missing "hole" value which will be returned as unused.

Mooneye

Answer 2

You can do:

select 1 + min(col)
from t
where not exists (select 1 from t t2 where t2.col = t.col + 1);

If you need to include "1", then:

select (case when min(tt.mincol) <> 1 then 1
             else 1 + min(col)
        end)
from t cross join
     (select min(col) as mincol from t) tt
where not exists (select 1 from t t2 where t2.col = t.col + 1)

Lehrer

This answer doesn't show how to include the additional condition on the customId column. – Rosarosabel