Sign up ×
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. It's 100% free, no registration required.

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

6 Answers 6

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