LeetCode: Find the Start and End Number of Continuous Ranges

Find the Start and End Number of Continuous Ranges

Similar Problems:

• CheatSheet: SQL & MySql
• CheatSheet: Leetcode For Code Interview
• CheatSheet: Common Code Problems & Follow-ups
• Tag: #sql

Table: Logs

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| log_id        | int     |
+---------------+---------+
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 query result format is in the following example:

Logs table:
+------------+
| log_id     |
+------------+
| 1          |
| 2          |
| 3          |
| 7          |
| 8          |
| 10         |
+------------+

Result table:
+------------+--------------+
| start_id   | end_id       |
+------------+--------------+
| 1          | 3            |
| 7          | 8            |
| 10         | 10           |
+------------+--------------+
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.

Github: code.dennyzhang.com

Credits To: leetcode.com

Leave me comments, if you have better ways to solve.

• Solution: Add temporary columns of rank and prev

## https://code.dennyzhang.com/find-the-start-and-end-number-of-continuous-ranges
select min(log_id) as START_ID, max(log_id) as END_ID
from (select log_id,
        @rank := case when @prev = log_id-1 then @rank else @rank+1 end as rank,
        @prev := log_id as prev
    from Logs,
       (select @rank:=0, @prev:=-1) as rows) as tt
group by rank
order by START_ID

• Solution: Find the starting and ending sequences, then merge two as one table

## https://code.dennyzhang.com/find-the-start-and-end-number-of-continuous-ranges
## find the starting sequence: 1, 7, 10
## find the ending sequence: 3, 8, 10
## merge them as one table
select start_id, min(end_id) as end_id
from (select t1.log_id as start_id
    from logs as t1 left join logs as t2
    on t1.log_id-1 = t2.log_id
    where t2.log_id is null) tt_start join
    (select t1.log_id as end_id
    from logs as t1 left join logs as t2
    on t1.log_id+1 = t2.log_id
    where t2.log_id is null) tt_end
where start_id<=end_id
group by start_id