CODEWITHSUNDEEP
mysqlsqlcounter
demonoid
demonoid

Reputation: 326

MySQL close the number sequence gap or suggest new sequence element

My table looks like that:

 CREATE TABLE `Room` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `branch_id` int(10) unsigned DEFAULT NULL,
  `course_id` int(10) unsigned DEFAULT NULL,
  `occupied_hours` tinyint(1) DEFAULT '0',
  `number` tinyint(2) DEFAULT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8

Number here is like counter in combination with 2 more values: branch_id, course_id. So for example if table looks like that:

... | branch_id | course_id | number | ...
        1             1           1
        1             1           3

what I want to do is, when I insert to this table to fill either gap between number fields (in this case 2) or give the +1 number of maximum (if gap doesn't exist) like 4 in this case if there was 2 value also.

It'd be great if there was a way to create it as procedure. Not long sql. How can I achieve this? Any ready to go function?

Upvotes: 1

Views: 35

Answers (1)

Jakub Kania
Jakub Kania

Reputation: 16477

A simple LEFT JOIN should do

SELECT Coalesce(Min(number),1) AS number, branch_id, course_id
FROM
  (SELECT r1.number -1 AS number, r1.branch_id, r1.course_id
  FROM `Room` AS r1
  LEFT JOIN `Room` AS r2
    ON r1.branch_id = r2.branch_id 
      AND r1.course_id = r2.course_id
      AND (r1.number - 1) = r2.number
  WHERE r1.number > 1 AND r2.number IS NULL
  UNION ALL
  SELECT Max(number) + 1, branch_id, course_id
  FROM `Room`
  GROUP BY branch_id, course_id) AS q
GROUP BY branch_id, course_id

Upvotes: 1

Related Questions