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

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..

Code:

---

      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;

---

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

Code:

---

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]

---

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.

It works, thanks dee-U ;)

No problem. =)