SQL - Manually obtain the next available ID for the given format-CodePudding

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

For an example, please see the table below.

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

Here, say I want to get '7092' for the type 'F' as the next value. But if I use the MAX function, it will return '99000002'.

SELECT MAX(id) 1 FROM students;

If I use the type column, I could get '7091' for the type 'F', but that's not available since it is used by another type.

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

This also does not work.

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

I am not able to change the database structure. If so, for the given scenario, is there a way (single SELECT statement) to get the next available ID for the selected type (e.g.: type F)?
I use 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