· 7 years ago · Dec 15, 2018, 05:24 AM
1app | major | minor | patch
2------+-------+-------+--------
3cat | 2 | 15 | 0
4cat | 2 | 15 | 1
5cat | 2 | 2 | 0
6dog | 1 | 0 | 1
7dog | 1 | 7 | 2
8dog | 3 | 0 | 0
9fish | 2 | 2 | 5
10fish | 2 | 3 | 1
11fish | 2 | 11 | 0
12
13app | major | minor | patch
14------+-------+-------+--------
15cat | 2 | 15 | 1
16dog | 3 | 0 | 0
17fish | 2 | 11 | 0
18
19CREATE TABLE IF NOT EXISTS `my_table` (
20 `app` varchar(10) NOT NULL,
21 `major` int(11) NOT NULL DEFAULT '0',
22 `minor` int(11) NOT NULL DEFAULT '0',
23 `patch` int(11) NOT NULL DEFAULT '0'
24) ENGINE=InnoDB DEFAULT CHARSET=utf8;
25
26INSERT INTO `my_table` (`app`, `major`, `minor`, `patch`) VALUES
27 ('cat', 2, 15, 1),
28 ('cat', 2, 15, 0),
29 ('cat', 2, 2, 0),
30 ('dog', 1, 0, 1),
31 ('dog', 1, 7, 2),
32 ('dog', 3, 0, 0),
33 ('fish', 2, 2, 5),
34 ('fish', 2, 3, 1),
35 ('fish', 2, 11, 0);
36
37SELECT m.*
38FROM my_table AS m
39JOIN (SELECT app, MAX(major*1000000 + minor*1000 + patch) AS maxversion
40 FROM my_table
41 GROUP BY app) AS m1
42ON m.app = m1.app AND major*1000000 + minor*1000 + patch = maxversion
43
44SELECT t.app
45 FROM my_table t
46 GROUP BY t.app
47
48SELECT t.app
49 , MAX(t.major) AS major
50 FROM my_table t
51 GROUP BY t.app
52
53SELECT t2.app
54 , t2.major
55 , MAX(t2.minor) AS minor
56 FROM my_table t2
57 JOIN (
58 SELECT t.app
59 , MAX(t.major) AS major
60 FROM my_table t
61 GROUP BY t.app
62 ) t1
63 ON t2.app = t1.app
64 AND t2.major = t1.major
65 GROUP BY t2.app, t2.major
66
67SELECT t4.app
68 , t4.major
69 , t4.minor
70 , MAX(t4.patch) AS patch
71 FROM my_table t4
72 JOIN ( -- query from above goes here
73 SELECT t2.app
74 , t2.major
75 , MAX(t2.minor) AS minor
76 FROM my_table t2
77 JOIN ( SELECT t.app
78 , MAX(t.major) AS major
79 FROM my_table t
80 GROUP BY t.app
81 ) t1
82 ON t2.app = t1.app
83 AND t2.major = t1.major
84 GROUP BY t2.app, t2.major
85 ) t3
86 ON t4.app = t3.app
87 AND t4.major = t3.major
88 AND t4.minor = t3.minor
89GROUP BY t4.app, t4.major, t4.minor