Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have 2 tables like below

table 1: users

id   name 
1    John
2    Jane
3    Mery
4    Alpha

table 2 : notes

id   note         owner created
1    alpha alpha    1   2013-02-03 10:30
2    yeah yeah..    2   2013-02-03 10:35
3    crap crap..    2   2013-02-03 10:50
2    yeah yeah..    3   2013-02-03 10:50
3    blah blah..    3   2013-02-03 10:55

Now i want to show in a list all the users with their latest notes like below

id   name     note
1    John     alpha alpha
2    Jane     crap crap..
3    Mery     blah blah..
4    Alpha    

How can I get this in one query? (PS: I am using PHP for scripting)

share|improve this question

migrated from Feb 7 '13 at 7:04

This question came from our site for professional and enthusiast programmers.

What does this question have to do with database "administration"? – Salman A Feb 7 '13 at 7:32
@SalmanA Please look at our FAQ and /About page. We are also about advanced SQL/querying, and database design among other things. – JNK Feb 7 '13 at 13:37
up vote 1 down vote accepted

This should work:

SELECT U.Id, U.Name, N.Note
FROM Users U
   LEFT JOIN (SELECT Owner, Max(created) maxCreated
         FROM Notes
         GROUP BY Owner) MaxN ON U.Id = MaxN.Owner
   LEFT JOIN Notes N ON U.Id = N.Owner AND N.Created = MaxN.maxCreated

Good luck.

share|improve this answer
Thanks, But What if the note is empty for a user? like in the example user 4? – Maddy Feb 4 '13 at 7:35
Add a LEFT JOIN instead -- I'll edit... – sgeddes Feb 4 '13 at 7:36
Awesome , Thanks It worked :-) – Maddy Feb 4 '13 at 8:42

SQLFiddle demo

select users.*, notes.note from users
select owner,max(created) as mc from notes group by owner
) t on 
LEFT JOIN notes on t.owner=notes.owner and
share|improve this answer
SELECT,, b.note, MAX (b.createdDate)
FROM    users a
        LEFT JOIN notes b
            ON a.iD = b.Owner
group by 1,2,3
share|improve this answer

if you want to get the latest created date for each owner.

SELECT  a.*, x.*
FROM    users a
        LEFT JOIN
            SELECT  b.*
            FROM    notes b INNER JOIN
                        SELECT  owner, MAX(created) max_date
                        FROM    notes
                        GROUP   BY owner
                    ) c ON  b.owner = c.owner AND
                            b.created = c.max_date
        ) x ON a.ID = x.owner
share|improve this answer
Perhaps this post may be answer to your question… – Sumit Munot Feb 4 '13 at 7:13

try this also.

 SELECT ,,notes.created FROM users
    LEFT JOIN notes ON
    inner JOIN
                            SELECT  OWNER, MAX(created) max_date
                            FROM    notes
                            GROUP   BY OWNER) sq
    ON notes.created=sq.max_date
    and notes.owner=sq.OWNER
share|improve this answer

Use a combination of ORDER BY, LEFT JOIN and GROUP BY:

SELECT,, ordered_notes.note
FROM users
    SELECT owner, note
    FROM notes
    ORDER BY created DESC, id DESC
) AS ordered_notes ON = ordered_notes.owner

It returns one result when two notes by same user have same date.

SQL Fiddle

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.