开发者

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

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.


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 )


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;


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))
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜