CODEWITHSUNDEEP
sqlmysqlgaps-and-islands
Shruti Saxena
Shruti Saxena

Reputation: 1

How to check records that come one after another in MySQL

I have a table movie that has id, movie name, and status columns.

id name status
1 movie1 0
2 movie1 1
3 movie1 0
4 movie1 1
5 movie1 0
6 movie1 0
7 movie1 1
8 movie1 0
9 movie1 1
10 movie1 1
11 movie1 0
12 movie1 0
13 movie1 0

Suppose these IDs are seat numbers, and status = 0 means a vacant seat, while status = 1 means an occupied seat.

How can I select 3 consecutive vacant seats?

For example, IDs 11, 12, and 13 are consecutive vacant seats:

id name status
11 movie1 0
12 movie1 0
13 movie1 0

Upvotes: -5

Views: 110

Answers (2)

Zhiqiang Guo
Zhiqiang Guo

Reputation: 88

I made up a table with 6 rows in it

-- INIT database
CREATE TABLE movie (
  id INT AUTO_INCREMENT KEY,
  name VARCHAR(100),
  status int
);
select * from movie;

enter image description here

Find the first id of the 3 consecutive movies then use the result first as another table and fetch records whose id is between first.id and first.id+2

select m.* from movie m ,(SELECT t1.id
FROM movie t1
JOIN movie t2 ON t1.id = t2.id - 1 AND t2.status = 0
JOIN movie t3 ON t2.id = t3.id - 1 AND t3.status = 0
WHERE t1.status = 0) first where m.id>=first.id and m.id<=first.id+2;

enter image description here

Upvotes: -2

UnderIdentityCrisis
UnderIdentityCrisis

Reputation: 86

As @Shadow commented:

This is a "gaps and islands" problem...

Here is one way to implement in your scenario

WITH GroupedSeats AS (
    SELECT 
        id,
        name,
        status,
        id - ROW_NUMBER() OVER (PARTITION BY status ORDER BY id) AS grp
    FROM movie 
    WHERE status = 0
)
SELECT 
    id,
    name,
    status
FROM GroupedSeats
WHERE grp IN (
    SELECT grp 
    FROM GroupedSeats 
    GROUP BY grp 
    HAVING COUNT(*) >= 2
)
ORDER BY id;

However, considering the context of identifying available seats for a movie, this approach seems a bit off to me. There might be a more efficient way to manage movie seat availability.

Upvotes: 0

Related Questions