How to get last login of each user using mysql?

Question

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;

Would like a select which shows the following result:

Display the last time each usuario logged into the system (based on the date field), and the code field is intended for when it misses the login, then it would have to display the last valid login and last login attempt with error (the code would have the result erro).

Answer 1

ALL users with their latest successful 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

Answer 2

I don’t know what your concern is with performance, but I don’t think a consultation like this would 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

Answer 3

To query below will return a row for each codigo different from 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 reference between simple quotes.

Answer 4

I believe it would be something like this you need:

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

Here’s a working example.