Get the latest insert based on a certain column using mysql?

Question

This is an example of a mysql table:

    Name | Message     | Time   
    -----+-------------+-------------
    John | Hi          | 12/11 7:00PM 
    John | Bye         | 12/11 7:05PM 
    John | Hello World | 12/11 7:10PM 
    Jane | I'm fine    | 12/11 7:15PM 
    Jane | How are you | 12/11 7:20PM 
    Jane | Hello World | 12/11 7:25PM 
    Bob  | Hi          | 12/11 7:30PM 
    Bob  | I'm fine    | 12/11 7:35PM 
    Bob  | Hello World | 12/11 7:40PM

My question is what code will I use to call for the latest insert of each person in the name column?

Example of what I'm looking for:

    Name | Message     | Time   
    -----+-------------+-------------
    John | Hello World | 12/11 7:10PM 
    Jane | Hello World | 12/11 7:25PM 
    Bob  | Hello World | 12/11 7:40PM

Thank you in advance.

EDIT

The time column is set as a varchar datatype not an time data type.

Store dates using a date/time data type. Beyond that, this is the most frequently answered question under this tag — Strawberry, Dec 11 '15 at 08:11
do you want to get for a specific time gap around last 20 min — saurabh kamble, Dec 11 '15 at 08:11
create a column for auto-increment and the use SELECT * FROM table_name ORDER BY "auto-increment-colum-name" DESC LIMIT 1 — Muhammad Husnain Tahir, Dec 11 '15 at 08:14
Order by date and limit 1 per user. Eg: [How to select the most recent set of dated records from a mysql table](http://stackoverflow.com/questions/435703/how-to-select-the-most-recent-set-of-dated-records-from-a-mysql-table) — Afshin Ghazi, Dec 11 '15 at 08:17

score 2 · Accepted Answer · answered Dec 11 '15 at 08:12

2

A simple INNER JOIN between the original table and a query which finds the max times for each name should do the trick:

SELECT t1.Name, t1.Message, t1.Time
FROM your_table t1
INNER JOIN
(
    SELECT Name, MAX(Time) AS MaxTime
    FROM your_table
    GROUP BY Name
) t2
ON t1.Name = t2.Name AND t1.Time = t2.MaxTime

answered Dec 11 '15 at 08:12

Tim Biegeleisen

502,043
27
286
360

Ok how would I go about doing that if my time column was not a time datatype but a varchar? – Bigg_aye Dec 11 '15 at 08:27
You might want to consider changing that column to a date type so you can use it properly. – Tim Biegeleisen Dec 11 '15 at 08:29
That's what I figured. – Bigg_aye Dec 11 '15 at 08:30
I will once I'm done testing. – Bigg_aye Dec 11 '15 at 08:31
You can either try casting the column as is, or possibly extract out a portion and cast that. You rock man. – Tim Biegeleisen Dec 11 '15 at 08:31
No I'd rather change the datatype. – Bigg_aye Dec 11 '15 at 08:33
I'm confused on how to change the datatype. I see date, datetime, time and timestamp. – Bigg_aye Dec 11 '15 at 08:35
Look into `STR_TO_DATE()`. You may have some trouble because you have an irregular timestamp with no year present. – Tim Biegeleisen Dec 11 '15 at 08:39
Yep, just read an article about that. Thanks alot for all your help. I'll test then mark as answer if it works for me. – Bigg_aye Dec 11 '15 at 08:41
Actually the bigger challenge to your question is the conversion of your `varchar` date column. It's too bad you did not include this, in which case the question might not have been closed. – Tim Biegeleisen Dec 11 '15 at 08:43
sorry about that :( didnt know. – Bigg_aye Dec 11 '15 at 08:46

score 0 · Answer 2 · answered Dec 11 '15 at 08:15

You can join your table with a group by version of itself to get the desired max date:

 SELECT tbl.name, tbl.message, MaxDateTbl.MaxTime
    FROM mytable tbl
    INNER JOIN (
      SELECT name, message, max(time) AS MaxTime
      FROM mytable
      GROUP BY name
      ) MaxDateTbl ON MaxDateTbl.name= tbl.name AND MaxDateTbl.Time = tbl.MaxTime

Get the latest insert based on a certain column using mysql?

2 Answers2