Mysql Question posted in
The official MySQL documentation can be found here.

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
Tags:

2

Answers


  1. 0

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

    SELECT
  m1.id
FROM
  movie m1,
  movie m2,
  movie m3
WHERE
  m1.id = m2.id-1
AND m2.id = m3.id-1
AND m1.status = m2.status
AND m2.status = m3.status
;
    Login or Signup to reply.
  2. 0

    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.

    Hope this will help!

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search