Select unique record with latest created date

Question

| id    | user_id | created_at (datetime) |
| 1     | 1       | 17 May 2016 10:31:34  |
| 2     | 1       | 17 May 2016 12:41:54  |
| 3     | 2       | 18 May 2016 01:13:57  |
| 4     | 1       | 19 May 2016 07:21:24  |
| 5     | 2       | 20 May 2016 11:23:21  |
| 6     | 1       | 21 May 2016 03:41:29  |

How can I get the result of unique and latest created_at user_id record, which will be record id 5 and 6 in the above case?

What I have tried so far

So far I am trying to use group_by to return a hash like this:

Table.all.group_by(&:user_id)

#{1 => [record 1, record 2, record 4, record 6], etc}

And select the record with maximum date from it? Thanks.

Updated solution

Thanks to Gordon answer, I am using find_by_sql to use raw sql query in ror.

@table = Table.find_by_sql("Select distinct on (user_id) *
                            From tables
                            Order by user_id, created_at desc")

#To include eager loading with find_by_sql, we can add this

ActiveRecord::Associations::Preloader.new.preload(@table, :user)

am noob but an idea would be to try: Model.find(:id).order(:created_at).last but it only work for one user_id. Maybe adding pluck such as: Model.find(:id).order(:created_at).uniq.pluck(:user_id).last — Maxence, May 22 '16 at 01:19
Actually I think pluck wont work. maybe using : Model.order(:created_at).where(:user_id ...).last and loop it ? — Maxence, May 22 '16 at 01:36
Thanks! In the end I selected `distinct on` answer which can get unique user_id. — pyfl88, May 22 '16 at 02:05
`Table.select('user_id, MAX(created_at) AS created_at').group(:user_id).order('created_at DESC')` Mind `created_at` is a string here, since it's a synthetic value, not the column. Unable to add an answer. @erwin-brandstetter, do you think it makes sense to set additional `rails` label, since question is Rails-centric and using raw SQL is last resort? — phil pirozhkov, Feb 03 '17 at 05:00
@philpirozhkov: I reopened the question so you can post your answer. (Though your answer does not seem right. The Q asks for whole *records*.) Basics abut `DISTINCT ON`: http://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group — Erwin Brandstetter, Feb 03 '17 at 05:28

score 6 · Accepted Answer · edited Feb 04 '17 at 07:43

6

In Postrgres, you can use DISTINCT ON:

SELECT DISTINCT ON (user_id) *
FROM tables
ORDER BY user_id, created_at DESC;

I am not sure how to express this in ruby.

edited Feb 04 '17 at 07:43

phil pirozhkov

4,740
2
33
40

answered May 22 '16 at 01:10

Gordon Linoff

1,242,037
58
646
786

Please correct me if I'm wrong, but this can be expressed in MySQL syntax as: `SELECT created_at FROM tables GROUP BY user_id ORDER BY created_at DESC;` – phil pirozhkov Feb 04 '17 at 07:46
Problem is you cannot order by `created_at` only in Postgres, `user_id` should also be part of sort key. – phil pirozhkov Feb 04 '17 at 07:48
@philpirozhkov . . . Absolutely not. If you have a question about MySQL, you should research answers in that database or ask one yourself. – Gordon Linoff Feb 04 '17 at 13:53

score 1 · Answer 2 · answered Feb 04 '17 at 07:30

1

Table
  .select('user_id, MAX(created_at) AS created_at')
  .group(:user_id)
  .order('created_at DESC')

Notice created_at is passed in as string in order call, since it's a result of aggregate function, not a column value.

answered Feb 04 '17 at 07:30

phil pirozhkov

4,740
2
33
40

Volodymyr Balytskyy · Answer 3 · 2016-05-22T08:20:36.230

0

1) Extract unique users form the table

Table.all.uniq(:user_id)

2) Find all records of each user.

Table.all.uniq(:user_id).each {|_user_id| Table.where(user_id: _user_id)}

3) Select the latest created

Table.all.uniq(:user_id).each {|_user_id| Table.where(user_id: _user_id).order(:created_at).last.created_at}

4) Return result in form of: [[id, user_id], [id, user_id] ... ]

Table.all.uniq(:user_id).map{|_user_id| [Table.where(user_id: _user_id).order(:created_at).last.id, _user_id]}

This should return [[6,1], [2,5]]

edited May 22 '16 at 08:20

answered May 22 '16 at 01:49

Volodymyr Balytskyy

577
1
7
19

Does the 'all.uniq' bit don't risk to elude the lastly created records? – Maxence May 22 '16 at 01:53
@Maxence I updated the answer. I guess it will not, since pyfl88 needs the latest created_at for **each** user_id. By getting all unique user_ids I am not excluding nothing. In his example there are only 2 unique ids **[1,2]**, than I loop through records containing those user_id. – Volodymyr Balytskyy May 22 '16 at 08:27

Select unique record with latest created date

3 Answers3