How to get the last login of each user using mysql?

Navigation
1

I have the following table: 

CREATE TABLE 'adm_historico_acesso' (
  'id' int(10) UNSIGNED NOT NULL,
  'data' timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  'ip' varchar(100) NOT NULL,
  'usuario' varchar(100),
  'codigo' varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL
) ENGINE = MyISAM DEFAULT CHARSET = latin1;

I would like a select to show the following result:

Show the last time that every usuario logged in to the system (based on the date field), where the code field is destined for when it fails login , then it would have to display the last valid login and last attempt to login with error (the code would have the result erro ).

    
asked by anonymous 03.10.2017 / 14:11

4 answers

1

ALL users with their latest success and error logins: 

SELECT usuario, MAX(data) data, codigo
FROM adm_historico_acesso
WHERE codigo = 'erro'
GROUP BY usuario, codigo

UNION ALL

SELECT usuario, MAX(data) data, codigo
FROM adm_historico_acesso
WHERE codigo <> 'erro'
GROUP BY usuario, codigo
    
03.10.2017 / 14:42
0

I do not know what your performance concern is, but I think a query like this would not have a considerable loss using union ; if I understand correctly, you want the last login with and without error: 

select top 1 id, data, codigo
from adm_historico_acesso
where codigo <> 'erro'
order by data desc
  union
select top 1 id, data, codigo 
from adm_historico_acesso
where codigo = 'erro'
order by data desc
union
    
03.10.2017 / 14:29
0
The query below will return one line for every codigo other than a user: 

SELECT a.'codigo', MAX(a.'data') 'data' FROM 'adm_historico_acesso' a
WHERE a.'usuario' = ''
GROUP BY a.'codigo';

Just put the user's reference between single quotes.

    
03.10.2017 / 14:38
0

I think it would be something like this: 

select MAX(data) as top, DATE(data), id, ip, usuario, codigo from
 'adm_historico_acesso' where codigo = '404';

Here's a working example .

    
03.10.2017 / 14:42
Toggle using different ng-Style - AngularJS POST Request with OkHttp in JAVA