MySQL correlated subquery ordered by date&time

Question

Afternoon All, I'm a stackoverflow virgin so go easy on me please. I am having difficulty retrieving the information i need from a database and believe that a correlated subquery may be the answer but am not sure.

All info i need is from one table:

╔══════╦══════════════╦══════════════╦════════════╦══════╦═════════════════════╦═════════════╗
║ COL1 ║     COL2     ║   FILENAME   ║    COL4    ║ COL5 ║      DATETIME       ║    COL7     ║
╠══════╬══════════════╬══════════════╬════════════╬══════╬═════════════════════╬═════════════╣
║    2 ║ generaluser1 ║ shared2.txt  ║ Shared     ║ XP1  ║ 2013-04-04 19:23:09 ║ Checked In  ║
║    3 ║ generaluser1 ║ shared2.txt  ║ Shared     ║ XP1  ║ 2013-04-04 19:24:06 ║ Checked Out ║
║    4 ║ generaluser1 ║ shared2.txt  ║ Shared     ║ XP1  ║ 2013-04-04 19:24:20 ║ Checked In  ║
║    5 ║ generaluser1 ║ restdoc3.txt ║ Restricted ║ XP1  ║ 2013-04-04 19:25:04 ║ Checked In  ║
║    6 ║ generaluser1 ║ restdoc3.txt ║ Restricted ║ XP1  ║ 2013-04-04 19:26:08 ║ Checked Out ║
║    7 ║ generaluser1 ║ restdoc3.txt ║ Restricted ║ XP1  ║ 2013-04-04 19:26:21 ║ Checked In  ║
║    8 ║ generaluser1 ║ shared2.txt  ║ Shared     ║ XP1  ║ 2013-04-04 19:26:51 ║ Checked Out ║
║    9 ║ generaluser1 ║ restdoc3.txt ║ Restricted ║ XP1  ║ 2013-04-04 19:28:41 ║ Checked Out ║
║   10 ║ generaluser1 ║ restdoc3.txt ║ Restricted ║ XP1  ║ 2013-04-04 19:31:01 ║ Checked In  ║
║   11 ║ generaluser2 ║ restdoc3.txt ║ Restricted ║ XP2  ║ 2013-04-04 19:31:13 ║ Checked Out ║
║   12 ║ generaluser1 ║ shared2.txt  ║ Shared     ║ XP1  ║ 2013-04-04 19:33:03 ║ Checked In  ║
║   13 ║ generaluser2 ║ restdoc3.txt ║ Restricted ║ XP2  ║ 2013-04-04 19:33:28 ║ Checked In  ║
║   14 ║ generaluser2 ║ restdoc4.txt ║ Restricted ║ XP2  ║ 2013-04-04 19:33:36 ║ Checked In  ║
╚══════╩══════════════╩══════════════╩════════════╩══════╩═════════════════════╩═════════════╝

What i am looking to do is retrieve a single line for each document name (col2) where the datetime is the latest.

Therefore the results i'd like to retieve/output for the above rows would only be:

╔══════╦══════════════╦══════════════╦════════════╦══════╦═════════════════════╦════════════╗
║ COL1 ║     COL2     ║   FILENAME   ║    COL4    ║ COL5 ║      DATETIME       ║    COL7    ║
╠══════╬══════════════╬══════════════╬════════════╬══════╬═════════════════════╬════════════╣
║   12 ║ generaluser1 ║ shared2.txt  ║ Shared     ║ XP1  ║ 2013-04-04 19:33:03 ║ Checked In ║
║   13 ║ generaluser2 ║ restdoc3.txt ║ Restricted ║ XP2  ║ 2013-04-04 19:33:28 ║ Checked In ║
║   14 ║ generaluser2 ║ restdoc4.txt ║ Restricted ║ XP2  ║ 2013-04-04 19:33:36 ║ Checked In ║
╚══════╩══════════════╩══════════════╩════════════╩══════╩═════════════════════╩════════════╝

I can manage to get the latest row for a doc of my choice or for all docs, but not the latest row for each unique doc.

Thanks in advance to anyone who helps me.

Answer 1

Let's say the column name that contains file name is FileName and the column which contains date and time is DateTime.

SELECT  a.*
FROM    TableName a
        INNER JOIN
        (
            SELECT  FileName, MAX(DateTime) max_date
            FROM    TableName
            GROUP   BY FileName
        ) b ON a.FileName = b.FileName AND
                a.DateTime = b.max_Date

• SQLFiddle Demo

THE OUTPUT

╔══════╦══════════════╦══════════════╦════════════╦══════╦═════════════════════╦════════════╗
║ COL1 ║     COL2     ║   FILENAME   ║    COL4    ║ COL5 ║      DATETIME       ║    COL7    ║
╠══════╬══════════════╬══════════════╬════════════╬══════╬═════════════════════╬════════════╣
║   12 ║ generaluser1 ║ shared2.txt  ║ Shared     ║ XP1  ║ 2013-04-04 19:33:03 ║ Checked In ║
║   13 ║ generaluser2 ║ restdoc3.txt ║ Restricted ║ XP2  ║ 2013-04-04 19:33:28 ║ Checked In ║
║   14 ║ generaluser2 ║ restdoc4.txt ║ Restricted ║ XP2  ║ 2013-04-04 19:33:36 ║ Checked In ║
╚══════╩══════════════╩══════════════╩════════════╩══════╩═════════════════════╩════════════╝