List last logged in users (GROUP BY AND ORDER BY)

Question

I have two tables (user and log), and would like to list users ordering by last logged in. I am using the GROUP BY to join the logs (since a user may own more than one).

The problem occurs when I add the ORDER BY, returning error in query:

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'insidetv.log.data' which is not functionally dependent on Columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

SQL:

SELECT nome, log.data FROM usuario JOIN log ON usuario.codigo = log.codigo_usuario GROUP BY usuario.codigo ORDER BY log.data

Answer 1

To list the last ones (decreasing), simply add the clause ORDER BY DESC, where:

DESC: Descending
ASC: Ascendant

By default, the language SQL uses the ASC (ascending/ascending form).

SQL:

SELECT nome, max(log.data) as last_data FROM usuario JOIN log ON usuario.codigo = log.codigo_usuario GROUP BY usuario.codigo ORDER BY last_data DESC