Get a list of first record for each group

Question

I have a Log model with following columns:

["id", "username", "event", "parameters", "extras", "created_at", "updated_at"]

I have indexes created_at and username.
Now, I would like to get the first log for each username ordered by created_at.

One way to do this is to run the following query for each username:

log = Log.where("username = :username", username: username).order(:created_at).first

But this obviously queries the database a lot of times (equal to the number of usernames). Is there some way to do only one database query?

Answer 1

Another case for DISTINCT ON:

SELECT DISTINCT ON (username) *
FROM   log
ORDER  BY username, created_at;

Returns the whole row for the "first" entry per username.

Details:

• Select first row in each GROUP BY group?

Similar answer for Ruby / AR / Postgres:

• Display latest messages from messages table, group by user

How to execute raw SQL:

• Table join sql to rails active record query

This Ruby syntax should work:

Log.select("DISTINCT ON (username) *").order(:username, :created_at)