CODEWITHSUNDEEP
mysqlsqlsubqueryleft-join
Al.G.
Al.G.

Reputation: 4407

Count rows after specific one

A table users has three columns: id, name, pass.

Another table logins has user_id column, an isright boolean (tinyint) column which says whether the login was successful or not and a date column.

I need a simple left join to get the user's name and his password (1), the last login datetime (successful or not) (2) and the count of the logins for the specific user since his last successful login (3).

(1) and (2) I can achieve using

SELECT name, pass, MAX(date)
FROM users
LEFT JOIN logins ON logins.id = users.id
-- here either "GROUP BY users.id" or "WHERE users.id = 1234"

But (3) seems to be harder. I googled it and found many similar question but none of them was asking on exactly how to count rows after specific condition is true. (It's even more complicated - count the logins for that user, not everyone)

I don't even know how to do it in a separate query (I'd prefer having one query for the 3 things and I suppose I'd have to use a subquery, although I prefer joins).

SQL fiddle with the tables and some data: http://sqlfiddle.com/#!9/a932b

Any ideas?

Upvotes: 0

Views: 94

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 95072

The straight-forward way is to have two derived tables: One to get the last login date per user, the other to get the last successful login date per user. Then select from users, outer join the two derived tables and look whether the last login was successful and count the (failed) logins after the last successful login. (With another DBMS you would rather use analytic functions that MySQL lacks.)

select 
  users.name, 
  users.pass, 
  (
    select max(isright)
    from logins 
    where user_id = students.id and date = last_login.date
  ) as last_login_successful,
  (
    select count(*)
    from logins
    where user_id = students.id and date > last_successful_login.date
  ) as last_logins_failed
from users
left outer join
(
  select user_id, max(date) as date
  from logins
  group by user_id
) last_login on last_login.user_id = users.id
left outer join
(
  select user_id, max(date) as date
  from logins
  where isright = 1
  group by user_id
) last_successful_login on last_successful_login.user_id = users.id;

This gives you four possibilities per user:

  1. The user never tried to login. last_login_successful is null and last_logins_failed is meaningless.
  2. The user's logins all failed. last_login_successful is 0 and last_logins_failed is meaningless.
  3. The user's last login was successful. last_login_successful is 1 and last_logins_failed is meaningless.
  4. The user logged in successfully once, but failed at least th last time they tried. last_login_successful is 0 and last_logins_failed is the number of failures after last successful login.

And here is a fiddle: http://sqlfiddle.com/#!9/57b7d/1.

EDIT: To also count failed logins when a user never logged in: If a user never logged in, their last_login.date is null. In last_logins_failed you want to count all records for which the last login occurred before OR never:

  (
    select count(*)
    from logins
    where user_id = students.id and (date > last_successful_login.date
    or last_successful_login.date is null)
  ) as last_logins_failed

Upvotes: 1

Jeremy C.
Jeremy C.

Reputation: 2465

I guess you could do something like 

select count(*)
from logins as l join users as u on l.user_id = u.id
where l.timestamp > (select max(timestamp)
                     from logins
                     where user_id = u.id and isright = 1)

Get the last timestamp of the login that was successful (subquery) for user then get a count for all the logins where the timestamp is greater than that of the user, this automatically gives you only the unsuccessful logins because you took the last successful one as a reference timestamp/datetime

Upvotes: 1

Hemang
Hemang

Reputation: 1671

How about using a view which only has latest login detail with below query

select l.user_id, l.date, l.isright from logins l
where l.date >= (select max(l2.date) from logins l2
    where l2.isright=1 and l2.user_id = l.user_id)

Upvotes: 0

Related Questions