Find the Start and End Number of Continuous Ranges LeetCode Solution

This Leetcode problem Find the Start and End Number of Continuous Ranges LeetCode Solution is done in SQL.

List of all LeetCode Solution

Problem Statement

id is the primary key for this table.
Each row of this table contains the ID in a log Table.

Since some IDs have been removed from Logs. Write an SQL query to find the start and end number of continuous ranges in table Logs.
Order the result table by start_id.

The result format is in the following example.

Example 1:
Input:

Output:

Explanation:
The result table should contain all ranges in table Logs.
From 1 to 3 is contained in the table.
From 4 to 6 is missing in the table
From 7 to 8 is contained in the table.
Number 9 is missing in the table.
Number 10 is contained in the table.

Find the Start and End Number of Continuous Ranges LeetCode Solution MySQL

select
l1.log_id as start_id,
l2.log_id as end_id
from
(
select
log_id
from
Logs
where
log_id - 1 not in (
select
*
from
Logs
)
) as l1,
(
select
log_id
from
Logs
where
log_id + 1 not in (
select
*
from
Logs
)
) as l2
where
l1.log_id <= l2.log_id
group by
l1.log_id;Code language: SQL (Structured Query Language) (sql)

Find the Start and End Number of Continuous Ranges LeetCode Solution MySQL (Another approach)

select
min(log_id) as start_id,
max(log_id) as end_id
from
(
select
*,
(@id := @id + 1) as id
from
logs,
(
select
@id := 0
) as init
) tmp
group by
log_id - id
Code language: SQL (Structured Query Language) (sql)
Scroll to Top