获取第一条记录的列表中为每个组 [英] Get a list of first record for each group

问题描述

我有一个登录模型下列：

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

现在，我想获得的第一个日志每个用户名按 created_at 有序。

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?

推荐答案

为 DISTINCT ON另一种情况：

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

给你的每个用户名。

详细信息：
<一href="http://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group/7630564#7630564">Select在每一组中第一行按组？

Details:
Select first row in each GROUP BY group?

为Ruby / AR / Postgres的类似的回答：
<一href="http://stackoverflow.com/questions/23728320/display-latest-messages-from-messages-table-group-by-user/23728690#23728690">Display用户从信息表，组​​最新消息

Similar answer for Ruby / AR / Postgres:
Display latest messages from messages table, group by user

如何执行原始SQL：
<一href="http://stackoverflow.com/questions/10633412/table-join-sql-to-rails-active-record-query/10639918#10639918">Table加入SQL到Rails活动​​记录查询

How to execute raw SQL:
Table join sql to rails active record query

我不是一个Ruby专家，但这个的Ruby语法应该工作：

I am not a Ruby expert, but this Ruby syntax should work:

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

这篇关于获取第一条记录的列表中为每个组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！