Mysql Get Max version of each file

Question

+----+--------+---------+---------------------+---------------------+-----------+
| id | FileId | version | createdAt           | updatedAt           | parent_id |
+----+--------+---------+---------------------+---------------------+-----------+
|  1 |      1 |       0 | 2014-12-01 11:24:48 | 2014-12-01 11:24:48 |         0 |
|  2 |      2 |       1 | 2014-12-01 11:25:16 | 2014-12-01 11:25:16 |         1 |
|  3 |      3 |       2 | 2014-12-01 11:25:30 | 2014-12-01 11:25:30 |         1 |
|  4 |      4 |       0 | 2014-12-06 13:09:26 | 2014-12-06 13:09:26 |         0 |
|  5 |      5 |       1 | 2014-12-06 13:10:48 | 2014-12-06 13:10:48 |         4 |
| 12 |     12 |       0 | 2014-12-06 13:35:02 | 2014-12-06 13:35:02 |         0 |
| 13 |     13 |       1 | 2014-12-06 13:35:14 | 2014-12-06 13:35:14 |        12 |
| 14 |     14 |       2 | 2014-12-06 13:47:06 | 2014-12-06 13:47:06 |        12 |
| 15 |     15 |       3 | 2014-12-06 13:52:42 | 2014-12-06 13:52:42 |        12 |
| 16 |      6 |       0 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |         0 |
| 17 |     16 |       0 | 2014-12-06 16:21:43 | 2014-12-06 16:21:43 |         0 |
| 18 |     17 |       1 | 2014-12-06 16:22:19 | 2014-12-06 16:22:19 |        16 |
| 19 |     18 |       0 | 2014-12-09 12:48:37 | 2014-12-09 12:48:37 |         0 |
| 20 |     19 |       1 | 2014-12-09 12:49:04 | 2014-12-09 12:49:04 |        18 |
| 21 |     20 |       0 | 2014-12-09 12:49:52 | 2014-12-09 12:49:52 |         0 |
| 22 |     21 |       1 | 2014-12-09 12:50:06 | 2014-12-09 12:50:06 |        20 |
| 23 |     22 |       2 | 2014-12-09 12:50:27 | 2014-12-09 12:50:27 |        20 |
| 24 |     23 |       0 | 2014-12-10 11:37:42 | 2014-12-10 11:37:42 |         0 |
| 25 |     24 |       0 | 2014-12-10 11:37:48 | 2014-12-10 11:37:48 |         0 |
+----+--------+---------+---------------------+---------------------+-----------+

This is my table I wan to find the max version with respect to each parent_id What i want to achieve I just want to find out the last file of each parent i.e parent_id = 0 means this is the root file else the file containing parent_id shows the successive version that File. I just want the output like this

+----+--------+---------+---------------------+---------------------+-----------+
| id | FileId | version | createdAt           | updatedAt           | parent_id |
+----+--------+---------+---------------------+---------------------+-----------+
|  3 |      3 |       2 | 2014-12-01 11:25:30 | 2014-12-01 11:25:30 |         1 |
|  5 |      5 |       1 | 2014-12-06 13:10:48 | 2014-12-06 13:10:48 |         4 |
| 15 |     15 |       3 | 2014-12-06 13:52:42 | 2014-12-06 13:52:42 |        12 |
| 16 |      6 |       0 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |         0 |
| 18 |     17 |       1 | 2014-12-06 16:22:19 | 2014-12-06 16:22:19 |        16 |
| 19 |     18 |       0 | 2014-12-09 12:48:37 | 2014-12-09 12:48:37 |         0 |
| 20 |     19 |       1 | 2014-12-09 12:49:04 | 2014-12-09 12:49:04 |        18 |
| 23 |     22 |       2 | 2014-12-09 12:50:27 | 2014-12-09 12:50:27 |        20 |
| 24 |     23 |       0 | 2014-12-10 11:37:42 | 2014-12-10 11:37:42 |         0 |
| 25 |     24 |       0 | 2014-12-10 11:37:48 | 2014-12-10 11:37:48 |         0 |
+----+--------+---------+---------------------+---------------------+-----------+

http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html — Abhik Chakraborty, Dec 10 '14 at 12:30

score 1 · Answer 1 · answered Dec 10 '14 at 12:31

1

Try:

Select max(version), parent_id from `table` group by parent_id

answered Dec 10 '14 at 12:31

Jens

67,715
15
98
113

Mysql Get Max version of each file

1 Answers1