SQL - Get the next available ID manually-CodePudding

I'm tying to construct a single, simple SQL SELECT statement that returns the next unique available ID manually for the given format.

For example, please refer to the following table.

-- Students (Table)
-- ID - Not a primary key
-- Type - Numbering format
 ---------- ------ 
|    ID    | Type |
 ---------- ------ 
|        1 | M    |
|        2 | M    |
|        5 | M    |
|     7056 | F    |
|     7057 | F    |
|     7058 | F    |
|     7090 | F    |
|     7091 | N    |
|    10910 | N    |
|    10911 | N    |
| 99000000 | O    |
| 99000001 | O    |
 ---------- ------

-- Some of the available values:
 --- ------ ------- ---------- 
| M |  F   |   N   |    O     |
 --- ------ ------- ---------- 
| 6 | 7092 | 10912 | 99000002 |
| 7 | 7093 | 10913 | 99000003 |
| 8 | 7094 | 10914 | 99000004 |
 --- ------ ------- ----------

In here, say, I want to get '7092' (for type 'F') as the next value. But if I use MAX function, it is gonna return me '99000002'.

SELECT MAX(id) 1 FROM students;

If I use type column, I could get '7091' for type 'F', but that's not available. (Used by another format/type)

SELECT MAX(id) 1 FROM students WHERE type = 'F';

This is also not working.

SELECT MAX(id) 1 
FROM   students 
WHERE  type = 'F'
       AND id NOT IN ( SELECT DISTINCT id FROM students)

Is there an easy way (single SELECT statement) to get the next available ID for given format?
I'm using oracle 10g, but MySQL and SQL Server are okay as well.

CREATE TABLE students (
  id   NUMBER,
  type CHAR(1)
);

INSERT INTO students VALUES (1, 'M');
INSERT INTO students VALUES (2, 'M');
INSERT INTO students VALUES (5, 'M');

INSERT INTO students VALUES (7056, 'F');
INSERT INTO students VALUES (7057, 'F');
INSERT INTO students VALUES (7058, 'F');
INSERT INTO students VALUES (7090, 'F');

INSERT INTO students VALUES (7091, 'N');
INSERT INTO students VALUES (10910, 'N');
INSERT INTO students VALUES (10911, 'N');

INSERT INTO students VALUES (99000000, 'O');
INSERT INTO students VALUES (99000001, 'O');

I would be grateful for any assistance. In any case, thank you for your time.

CodePudding user response：

You're looking for the gaps, in consequence you're looking for the values NOT IN the id list.

The following query will give you a list of all the id's NOT IN the table.

SELECT sub_id FROM (
    SELECT id 1 as sub_id FROM students
)sub_table WHERE sub_id NOT IN (
    SELECT id FROM students
);

Then you can select the MIN() of that list:

SELECT MIN(sub_id) FROM (
    SELECT id 1 as sub_id FROM students
)sub_table WHERE sub_id NOT IN (
    SELECT id FROM students
);

Bear in mind that this will work only if you already have at least one id and it won't start from 1 unless you already have the id 1.

Notice: that the first query only gives you the list of all the gaps plus the next available id.

CodePudding user response：

You can use:

SELECT COALESCE(MAX(id)   1,1) AS next_id
FROM   (
  SELECT id,
         ROW_NUMBER() OVER (ORDER BY id) AS rn
  FROM   students
)
WHERE id = rn;

or:

SELECT COALESCE(MAX(id   1), 1) AS next_id
FROM   students
WHERE  CONNECT_BY_ISLEAF = 1
START WITH id = 1
CONNECT BY PRIOR id   1 = id;

Which, for your sample data, both output:

NEXT_ID

3

NEXT_ID
3

Note: These queries will both return 1 if the first id value is deleted.

However, a better solution would be to use a sequence and let that handle generating the keys and not to worry about gaps between values.

db<>fiddle here