Create a scope that groups unique id and recent date

I want to write a scope that returns the most recent (created_at) row
for each person_id

So this table

id person_id created_at
1 111 2013-12-01 08:00
2 222 2013-11-20 07:00
3 111 2013-12-10 09:00
4 333 2013-10-21 08:00
5 333 2013-10-15 08:00

should return this

2 222 2013-11-20 07:00
3 111 2013-12-10 09:00
4 333 2013-10-21 08:00

Can someone help me out?
Many thanks

Sorry, I just don’t know how to do it.

You’de better ask on [email protected]

Abinoam Jr.

On Wed, Dec 4, 2013 at 3:02 AM, Bo Pritchard [email protected]
wrote:

5 333 2013-10-15 08:00

should return this

2 222 2013-11-20 07:00
3 111 2013-12-10 09:00
4 333 2013-10-21 08:00

– untested
SELECT t.id, t.person_id, t.created_at
FROM your_table t
JOIN (
SELECT person_id, MAX(created_at) as cat
FROM your_table
GROUP BY t.person_id
) m ON m.person_id = t.person_id
AND m.cat = t.created_at

Cheers

robert

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs