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

Question

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 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.

Answer 1

This will work:

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

Answer 2

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

not work?

Answer 3

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)