Mysql query get data if certain column is on sequence

Question

What I want here is to retrieve pid's that have these criteria: pid count is atleast 3 and sid should atleast contain two numbers that are in sequence. For example sid should 1 & 2, 2 & 3, 3 & 4, and so on.

Table sem_attendance
attendance_id   sid    pid
            1     1      3
            6     1     12 
            8     2     12 
            9     4     12 
           10     1     23 
           11     3     23 
           12     1     29 
           13     3     27  
           14     2     24 
           15     3     21 
           16     2     21 
           17     1     27 
           18     2     27
           19     5     23

My query:

SELECT attendance_id, sid, sem_attendance.pid
FROM sem_attendance
INNER JOIN (
    SELECT pid
    FROM sem_attendance
    GROUP BY pid HAVING COUNT( pid ) =3)
temp ON sem_attendance.pid = temp.pid
ORDER BY temp.pid, sem_attendance.sid


attendance_id   sid    pid  
            6     1     12
            8     2     12
            9     4     12
           10     1     23
           11     3     23
           19     5     23
           17     1     27
           18     2     27
           13     3     27

Desired output should only be pid's 12 and 27 because the sid's 1, 3 and 5 are not in sequence. Is it possible to do this on mysql? If not, how about on php coding? Help me please.

Which column defines order in your table?

Giorgos Betsos
– Giorgos Betsos

2016-02-17 20:29:47 +00:00
Commented Feb 17, 2016 at 20:29 — Giorgos Betsos
– Giorgos Betsos, Commented Feb 17, 2016 at 20:29

Joachim Isaksson · Accepted Answer · 2016-02-17 20:40:12Z

2

You can split the two conditions into two separate subqueries and just pick rows with the pids that exist in both sets;

SELECT * FROM sem_attendance
WHERE pid IN (                           -- at least 3 in the group
  SELECT pid FROM sem_attendance
  GROUP BY pid HAVING COUNT(1) >= 3
)
AND pid IN (                             -- at least 2 consecutive
  SELECT a.pid FROM sem_attendance a JOIN sem_attendance b
    ON a.pid = b.pid AND a.sid = b.sid + 1
)

An IDEone sample to test with.

edited Feb 17, 2016 at 20:40

answered Feb 17, 2016 at 20:34

Joachim Isaksson

182k28 gold badges297 silver badges307 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

fmpsagara Over a year ago

Thank you very much for answering! You nailed it! Never thought that was possible!

Giorgos Betsos · Accepted Answer · 2016-02-17 20:52:59Z

2

You can use the following query:

SELECT attendance_id, sid, pid 
FROM (
  SELECT attendance_id, sid, pid, 
         (SELECT sid
          FROM sem_attendance AS t2
          WHERE t1.pid = t2.pid AND t1.attendance_id < t2.attendance_id
          ORDER BY attendance_id LIMIT 1) AS next_sid
  FROM sem_attendance AS t1
  WHERE pid IN (SELECT pid
               FROM sem_attendance
               GROUP BY pid
               HAVING COUNT(*) >= 3)) AS t
WHERE sid = next_sid - 1

I've made the assumption that field attendance_id defines order in your table. Consecutive table records are deduced based on this assumption.

edited Feb 17, 2016 at 20:52

answered Feb 17, 2016 at 20:42

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

1 Comment

fmpsagara Over a year ago

Perfect answer as well! Wished I could mark both as my answer. You earned my respect sir!

Collectives™ on Stack Overflow

Mysql query get data if certain column is on sequence

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related