MYSQL counting rows until last different row

Question

I have an event table in the form:

|  User  |     Event         |   Date  |
| User A | Failed Log In     |   ...   | 
| User A | Failed Log In     |   ...   |
| User B | Failed Log In     |         |
| User A | Successful Log In |         |
| User B | Successful Log In |         |

What I would like to do is to count all the 'failed log ins' for each user since the last 'successful log in' so I can create a view in the form:

|  User  | Failed Log in attempts |
| User A |            2           |
| User B |            1           |

Is this possible to do in SQL? Or do I need to pull this logic out into my app?

One must assume that your table has an id or date/time column specifying the ordering. — Gordon Linoff, Feb 27 '16 at 13:58

Gordon Linoff · Accepted Answer · 2016-02-27T21:48:03.853

SQL tables represent unordered sets, so there is no "last" or "before" unless a column specifies the ordering. So, this answer assumes some sort of eventdatetime column with that information, although an auto incrementing id would work just as well (and possibly better).

I think this does what you want:

select e.user, count(*) as NumFailedAttempts
from events e
where e.event = 'Failed Log In' and
      e.eventdatetime > (select max(e2.eventdatetime)
                         from events e2
                         where e2.user = e.user and e2.event = 'Successful Log In'
                        );

If you want to include zeros (those with no failed logins), then you can left join this to the users table.

@quasoft . . . That makes sense in terms of the text (and I changed the answer). I was looking at the table and assuming the newer rows were at the bottom, rather than the top. — Gordon Linoff, Feb 27 '16 at 21:48

score 1 · Answer 2 · edited May 23 '17 at 11:59

An alternative to @GordonLinoff answer (which is correct), could be to join the table to itself, in order to get the last login time, instead of using a subquery:

SELECT events.user, COUNT(events.event) AS FailedCount
FROM events
LEFT JOIN (
  SELECT MAX(`date`) AS lastlogin
  FROM events
  WHERE event = 'Success'
) AS last ON (events.user = last.user)
WHERE
  event = 'Failed'
  AND
  `date` > last.lastlogin
GROUP BY user, event

http://sqlfiddle.com/#!9/96faa/1/0

What this does is:

Join the events table to itself, thus adding a temporary lastLogin field to each row;
Select failed events that occured after last login;
Group filtered list of failed events in order to count them.

Short introduction to joining a table to itself is available in MySQL documentation: Using More Than one Table and in this article: Joining a Table to Itself.

This could give minimal performance benefit over subquery variant. Actually it is subjective and depends on the specific version of the DBMS and "mood" of query optimizer.

Discussions about performance of joins vs subqueries can be found here:

Join vs. sub-query

SQL Joins Vs SQL Subqueries (Performance)?

JOINs vs Subqueries, Chris London

MYSQL counting rows until last different row

2 Answers2