Group by selects first occurence of field how can I get the last one?

SELECT user_id, created FROM photo_comments GROUP BY user_id

returns

  user_id  created  
  1        2009-10-20 21:08:22 
  12       2009-10-20 21:45:00 
  16       2009-10-28 20:35:30 

But that created date of 2009-10-20 for user id 1 is the first entry by user id 开发者_Go百科1.

How can I get the last entry by user id 1? (date should be 2009-11-02)

I'm trying to get the last entry by each user in order of most recent first.

---

This will work:

SELECT user_id, max(created) FROM photo_comments GROUP BY user_id

---

Ordinary SQL isn't supposed to allow that sort of query in the first place. In theory, you can only request a field that you named in a GROUP BY clause or an aggregate function of some other field. Here's how you'd build your modified query. (Then you can add other fields too on the first line.)

SELECT user_id, created
FROM photo_comments o
WHERE created =
    (SELECT MAX(created) 
    FROM photo_comments i
    WHERE o.user_id=i.user_id)

---

does SELECT user_id, created FROM photo_comments order by created desc GROUP BY user_id

not work?