LeetCode: Find the Start and End Number of Continuous Ranges Posted on August 5, 2019July 26, 2020 by braindenny 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 Post Views: 0