[RESOLVED] DB (Access or SQL Server) - Using pure SQL find first available Key in Table

[jcis]

How to get the first Key available in a given Table, assumming some entries has been deleted, there are gaps, i need an SQL Query to get the first available, examples:

1, 2, 3, 6, 7, 8, 10, 14, 15
(it should return 4)

6, 7, 8, 10, 14, 15
(it should return 1)

I did this one, but there is a problem, this query returns the Key available after the first gap, not before..

      SELECT MIN(a.CODE-1)
        FROM Table AS a 
  LEFT JOIN Table AS b ON a.CODE - 1 =  b.CODE
      WHERE b.CODE IS NULL AND a.CODE <> 1;

[dee-u]

I am not sure if this will work in Access but should work with SQL Server.

SELECT
       CASE
              WHEN MAX(code) = COUNT(*)
              THEN CAST(NULL AS INTEGER)
              WHEN MIN(code) > 1
              THEN 1
              WHEN MAX(code) <> COUNT(*)
              THEN
                     (SELECT MIN(code) + 1
                     FROM    [TABLE]
                     WHERE
                             (
                                     code + 1
                             )
                             NOT IN
                                    (SELECT code
                                    FROM    [TABLE]
                                    )
                     )
              ELSE NULL
       END
FROM   [TABLE]

[jcis]

Yes, that should work, i don't have SQL Server installed so i was testing with Access and i needed to add a SELECT CASE to do that but i don't know how to do it in Access, I'll install SQL Server now so I can test your code, thank you.

[jcis]

It works, thanks dee-U

[dee-u]

No problem. =)