· 8 years ago · Mar 04, 2018, 10:02 PM
1CREATE TABLE IF NOT EXISTS intersection (
2 cnt NUMERIC NOT NULL,
3 sweep NUMERIC)
4
5SELECT rowid, cnt, sweep FROM intersection
6WHERE cnt IN (SELECT MAX(cnt) FROM intersection)
7
8+--------+------+-------+
9| rowid | cnt | sweep |
10| 106 | 7 | 21.07 |
11| 107 | 7 | 21.17 |
12| 108 | 7 | 21.27 |
13| 109 | 7 | 21.37 |
14| 110 | 7 | 21.47 |
15| 111 | 7 | 21.57 |
16| 112 | 7 | 21.67 |
17| 113 | 7 | 21.78 |---split here
18| 152 | 7 | 25.7 |
19| 153 | 7 | 25.8 |
20| 154 | 7 | 25.9 |
21| 155 | 7 | 26 |
22| 156 | 7 | 26.1 |
23| 157 | 7 | 26.2 |
24| 158 | 7 | 26.31 |
25| 159 | 7 | 26.41 |
26| 160 | 7 | 26.51 |
27| 161 | 7 | 26.61 |
28+--------+------+-------+
29
30SELECT f.rowid, f.cnt, f.sweep FROM intersection f
31 WHERE cnt IN (SELECT MAX(cnt) FROM intersection)
32 AND NOT EXISTS (
33 SELECT * FROM intersection s
34 WHERE s.cnt = f.cnt
35 AND s.rowid = f.rowid+1)--or -1