Logan M. Logan M. - 27 days ago 15
SQL Question

How to select DISTINCT(username) and MAX(timestamp) from access log - postgresql

I have a log file for an application that shows two columns, username and timestamp. I'm trying to run a PostgreSQL query to show only the last time each user logged in.

This is the query I have put together so far:

SELECT DISTINCT username, MAX(timestamp) FROM log
GROUP BY username, timestamp;


Unfortunately, this is still giving me multiple username entries, each with a distinct time. Any tips on how to just show the last timestamp for each user?

I'm still new to SQL so any help would be appreciated. Thanks.

Answer

The problem is the group by:

SELECT username, MAX(timestamp)
FROM log
GROUP BY username;

However, if you want all the information from the log record, DISTINCT ON is a better choice:

SELECT DISTINCT ON (username) l.*
FROM log
ORDER BY username, timestamp DESC;

You use of SELECT DISTINCT leads me to suspect that this might be what you want.