MySQL get row closest to NOW()

Question

I have a table with User data such as name, address etc and another table which has a paragraph of text about the user. The reason that they are separate is because we need to record all the old about data. So if the user changes their paragraph - the old one should still be stored. Each bit of about data has a primary key aboutMeID. What I want to do is have a join that pulls their name, address etc and the latest bit of aboutMe data from the other table.

I am not sure though how I can order the join to only get the latest about me data.

Can someone help?

Answer 1

Assuming you have a column with the dateEntered field you could just say

select col1, col2, col3 from aboutme order by dateEntered desc limit 1

This will give you the row that is newest.

Answer 2

you should sort it by date of change descendently and limit it to one record (LIMIT 1)

Answer 3

Where you have two tables to join, the subquery asks the table with the older paragraphs to give just the last one, ordered by date (datestamp DESC) and match it up based on your user ID. Finally the outer query limits the whole thing to just the user in question (@UserID).

SELECT name, address, aboutme
FROM users
LEFT JOIN
    (SELECT aboutme FROM oldertable
    WHERE aboutMeID = users.id
    ORDER BY datestamp DESC
    LIMIT 1)
WHERE users.id = @UserID;

Answer 4

subselect variant:

select name, 
       address, 
      (select aboutme 
              from ABOUTTABLE 
              where USERDATA.userid = ABBOUTTABLE.userid order by datefield limit 1) 
 from USERDATA

Answer 5

This is rather tricky and probably would be slow but it is done through depended subquery.

SELECT name, address, aboutme.text as aboutme
FROM users
LEFT JOIN aboutme ON aboutMeID = (SELECT aboutMeID FROM aboutme
                                  WHERE user_id = users.id
                                  ORDER BY datestamp DESC
                                  LIMIT 1)

Not sure is it faster than @JYelton solution, it is something you'd have to test yourself.