title: SELECT data based on result of previous row in table
body: <p>I have a database of students.</p>
  
  <pre><code>    CREATE TABLE classlist
          (`id` int, `studentid` int, `subjectid` int, `presentid` int)
      ;
  
      CREATE TABLE student
          (`id` int, `name` varchar(4))
      ;
  
      CREATE TABLE subject
          (`id` int, `name` varchar(4))
      ;
  
      CREATE TABLE classStatus
          (`id` int, `name` varchar(8))
      ;
  
      INSERT INTO classlist
          (`id`, `studentid`, `subjectid`, `presentid`)
      VALUES
          (1, 111, 1, 1),
          (2, 222, 3, 0),
          (3, 333, 2, 1),
          (4, 111, 4, 1),
          (5, 111, 1, 0),
          (6, 222, 3, 0),
          (7, 333, 2, 1),
          (8, 111, 4, 1),
          (9, 111, 2, 0),
          (10, 111, 4, 1),
          (11, 111, 1, 1),
          (12, 333, 3, 1),
          (13, 333, 2, 1),
          (14, 333, 3, 1)
      ;
  
      INSERT INTO student
          (`id`, `name`)
      VALUES
      (111, 'John'),
      (222, 'Kate'),
      (333, 'Matt')
      ;
  
      INSERT INTO subject
          (`id`, `name`)
      VALUES
      (1, 'MATH'),
      (2, 'ENG'),
      (3, 'SCI'),
      (4, 'GEO')
      ;
  
      INSERT INTO classStatus
          (`id`, `name`)
      VALUES
      (0, 'Absent'),
      (1, 'Present')
      ;
  </code></pre>
  
  <p>And I have a query which shows how many times they have been present or absent.</p>
  
  <pre><code>    SELECT
         studentid,
         students.name AS NAME,
         SUM(presentid = 1) AS present,
         SUM(presentid = 0) AS absent
  
      FROM classlist
      INNER JOIN student as students ON classlist.studentid=students.id
  
       GROUP BY studentid, NAME
  </code></pre>
  
  <p>See this fiddle below.
  <a href="http://sqlfiddle.com/#!2/fe0b0/1" rel="nofollow">http://sqlfiddle.com/#!2/fe0b0/1</a></p>
  
  <p>There seems to be a trend from looking at this sample data that after someone attends subjectid 4 they are often not coming to the next class. How can I capture this in a query.   I want to ONLY show data WHERE last subjectid =4. So in my sample data rows matching my criteria would be.</p>
  
  <pre><code>    (5, 111, 1, 0),
      (9, 111, 2, 0),
      (11, 111, 1, 1),
  </code></pre>
  
  <p>as these rows are all the next row of a studentid who had a subjectid=4.</p>
  
  <p>My output would be </p>
  
  <pre><code>    | STUDENTID | NAME | PRESENT    | ABSENT|
      | 111       | John | 1          | 2     |
  </code></pre>