Counting # of consecutive rows with specified value(s)? - Mailing list pgsql-general

I'm working with an attendance table, where each person gets a record for each day of class, with an attendance code (ABSENT, ATTENDED, ...).  I'm trying to figure out how to get the number of consecutive absences a person has.  I'm guessing this can be done without writing a function, but I need some help here.

I can currently test whether someone has at least a specified number of consecutive absences with the query below, but it would be  better to get the actual number.

As a second question, what about getting the number of consecutive records for a set of values?  (e.g., attendance_code IN ('ATTENDED','EXCUSED')

Any ideas or suggestions?  Thanks.

Ken

This query checks for 4 consecutive absences:

SELECT client_id,

    array( 

      SELECT attendance_code

      FROM attendance

      WHERE client_id=enrollment.client_id

      ORDER BY attended_on DESC

      LIMIT 4

    )=array_fill('ABSENT'::varchar,array[4]) AS absent_last_4 

  FROM enrollment;