selecting unique ids from two rows

ok so table looks like this

owner_id | creator_id | receiver_id | content | created
1        | 1          | 2           | hoho    | 2011-27-05
2        | 1          | 2           | hoho    | 2011-27-05
1        | 2        开发者_如何学C  | 1           | eoeo    | 2011-28-05
2        | 2          | 1           | eoeo    | 2011-28-05
1        | 1          | 3           | aaaa    | 2011-29-05
3        | 1          | 3           | aaaa    | 2011-29-05
2        | 2          | 3           | bbbb    | 2011-30-05
3        | 2          | 3           | bbbb    | 2011-30-05

what I would like is to select only the last record for owner_id = 1 from each creator_id OR receiver_id and it doesn't matter is it last from creator_id or receiver_id as long it is last record where unique creator_id or receiver_id is.

do the result should look like this

owner_id | creator_id | receiver_id | content | created
1        | 1          | 3           | aaaa    | 2011-29-05
1        | 2          | 1           | eoeo    | 2011-28-05

---

Start by fetching the last date per owner_id, creator_id:

select owner_id,
       creator_id as user_id,
       max(created) as max_created
from data
where owner_id = 1
group by owner_id, creator_id

And the same per owner_id, receiver_id:

select owner_id,
       receiver_id as user_id,
       max(created) as max_created
from data
where owner_id = 1
group by owner_id, receiver_id

Then union and apply the max another time:

select owner_id, user_id, max(max_created) as max_created
from (
     [the first of the above]
     union all
     [the second of the above]
     ) as users
group by owner_id, user_id

Then join it in a subquery:

select data.*
from data
join ([the union query above]) as max_dates
on max_dates.owner_id = data.owner_id
and max_dates.user_id in (data.creator_id, data.reporter_id)
and max_dates.max_created = data.created
where owner_id = 1 -- avoids scanning the whole table

That'll yield the rows where creator_id appeared last, and those where reporter_id appeared last.

You cannot reduce it further without dropping valid rows. Suppose the returned (creator/reporter) pairs are (1,2) and (2,1). Then both rows would be eliminated if you sought the absolute last appearance of each user.