SQL query for finding row with same column values that was created most recently

Question

If I have three columns in my MySQL table people, say id, name, created where name is a string and created is a timestamp.. what's the appropriate query for a scenario where I have 10 rows and each row has a record with a name. The names could have a unique id, but a similar name none the less. So you can have three Bob's, two Mary's, one Jack and 4 Phil's.

There is also a hobbies table with the columns id, hobby, person_id.

Basically I want a query that will do the following:

Return all of the people with zero hobbies, but only check by the latest distinct person created, if that makes sense. Meaning if there is a Bob person that was created yesterday, and one created today.. I only want to know if the Bob created today has zero hobbies. The one from yesterday is no longer relevant.

Answer 1

Try This:

   Select *
   From people p
   Where timeStamp = 
         (Select Max(timestamp)
          From people 
          Where name = p.Name
             And not exists 
                 (Select * From hobbies
                  Where person_id = p.id))

Answer 2

select pp.id
from people pp, (select name, max(created) from people group by name) p
where pp.name = p.name
and pp.created = p.created
and id not in ( select person_id from hobbies )

Answer 3

SELECT latest_person.* FROM (
  SELECT p1.* FROM people p1 
  WHERE NOT EXISTS (
    SELECT * FROM people p2 
    WHERE p1.name = p2.name AND p1.created < p2.created
  )
) AS latest_person
LEFT OUTER JOIN hobbies h ON h.person_id = latest_person.id 
WHERE h.id IS NULL;