Last updated on October 9th, 2024 at 10:03 pm

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

List of all LeetCode Solution

Find the Start and End Number of Continuous Ranges 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.

1. 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;

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