Find specific sequences

from the Artful Common Queries page

You have a table which tracks hits on named web pages...

  page CHAR(128) DEFAULT '',
  PRIMARY KEY(id, time)

where id is unique to a session. Here is a bit of sample data:


You desire a count of the number of sessions where a user moved from one particular page directly to another, for example from 'A' to 'C'.

To find the next hit in a given session, scope on id, order by time, and limit the output to one row. Then simply count the rows meeting the page criteria:

  COUNT(DISTINCT AS 'Moves from A to C'
FROM hits AS h1
  AND 'C' = (
    FROM hits AS h2
    WHERE = 
      AND h2.time > h1.time
    ORDER BY h2.time LIMIT 1
| Moves from A to C |
|                 3 |

Return to the Artful Common Queries page