CODEWITHSUNDEEP
sqlruby-on-railsactiverecordgroup-by
Logan Serman
Logan Serman

Reputation: 29880

Finding the most recent entry per reference (foreign key) in Rails

Can't quite figure this one out. I have a table like this

user_id | time       |
----------------------
1       | 10         |
1       | 11         |
1       | 12         |
2       | 13         |
3       | 14         |

There is more to it than that and time is a datetime field, but that information should be irrelevant. I'd like to only pull the most recent record per user. So my result set would look like

user_id | time       |
----------------------
1       | 12         |
2       | 13         |
3       | 14         |

How would I do this using the Rails ActiveRecord interface? I'm assuming it has to do with GROUP BY, but I've never had a good grasp on those queries.

Upvotes: 1

Views: 133

Answers (2)

Anvesh
Anvesh

Reputation: 7723

Try this :

select user_id,MAX(time) as MaxTime from tableName Group by user_id

Upvotes: 1

Freelancer
Freelancer

Reputation: 9084

Use Following query:

select user_id,time from tableName where user_id=(select last(user_id) from tableName) group by user_id

Hope its helpful.

Upvotes: 0

Related Questions