CODEWITHSUNDEEP
mysqlsql
Tivie
Tivie

Reputation: 18923

Select last inserted row for a given index

I've made a small revision system that stores the new version of a text each time it is modified.

The table looks like this:

+-------+-----+----------+
| revID | nID |   text   |
+-------+-----+----------+
|   1   |  1  |   foo    |
+-------+-----+----------+
|   2   |  1  |  newfoo  |
+-------+-----+----------+
|   3   |  2  |   bar    |
+-------+-----+----------+
|   4   |  2  |   baz    |
+-------+-----+----------+
|   5   |  3  |    a     |
+-------+-----+----------+

What SQL statement will give me the last inserted row for every nID? Like this:

+-------+-----+----------+
| revID | nID |   text1  |
+-------+-----+----------+
|   2   |  1  |  newfoo  |
+-------+-----+----------+
|   4   |  2  |   baz    |
+-------+-----+----------+
|   5   |  3  |    a     |
+-------+-----+----------+

Upvotes: 0

Views: 70

Answers (1)

John Woo
John Woo

Reputation: 263723

The idea of creating a subquery is to get the maximum RevID for each NID, then joining it against the table itself but with two joining condition: that it match with NID and that it also match with RevID

SELECT  a.*
FROM    tableName a
        INNER JOIN
        (
            SELECT nid, MAX(revID) maxID
            FROM tableName
            GROUP BY nid
        ) b ON a.nid = b.nid AND
                a.revID = b.maxID

Upvotes: 3

Related Questions