Retreiving last row of records, if it is the most recent?

[scooter41]

Ok, so I really didnt know how to title that subject!

 

I have a very simple table, which essentially is for a simple application where a user can 'own' another user, and someone else can then steal ownership off of that user.

 

The 3 field table is:

 

sender (a userID)

owner (a userID)

time

 

All steals/ownerships have to be recorded for counting purposes.

 

I have a page that looks up who that specific user currently owns, and at present I use:

 

SELECT DISTINCT *  FROM ownership WHERE sender='675490779' GROUP BY owner,sender limit 0,10

 

But this of course only shows anyone that they have owned previously, rather than who they currently own if no-one else has stolen since.

 

I need that query to be written such that it retreives the row, but only if they are the most recent owner when sorted by time.

 

Does this make any kind of sense, have I made a simple problem confusing?

 

Thanks for any help in advance

 

Scott

 

 

 

 

[fenway]

First, you don't need DISTINCT and GROUP BY.

Second, you can't ask for the records to be group in any particular order.

Third, why not get back the current owners and then filter by that sender?  Otherwise, I guess you could do it the other way, and check for any newer owners with a join.