sqlite - Distinguish a column with the same value by range of id and count this range, take the higher one

I have a small sqlite table

``````CREATE TABLE IF NOT EXISTS intersection (
cnt NUMERIC NOT NULL,
sweep NUMERIC)
``````

from what i want to get the maximum value of "cnt" and later on the average of "sweep" depending on the range/amount of distinct "cnt"-values.

Since sqlite only has this rowid option i use it within my query:

``````SELECT rowid, cnt, sweep FROM intersection
WHERE cnt IN (SELECT MAX(cnt) FROM intersection)
``````

This returns:

``````+--------+------+-------+
| rowid  | cnt  | sweep |
| 106    | 7    | 21.07 |
| 107    | 7    | 21.17 |
| 108    | 7    | 21.27 |
| 109    | 7    | 21.37 |
| 110    | 7    | 21.47 |
| 111    | 7    | 21.57 |
| 112    | 7    | 21.67 |
| 113    | 7    | 21.78 |---split here
| 152    | 7    | 25.7  |
| 153    | 7    | 25.8  |
| 154    | 7    | 25.9  |
| 155    | 7    | 26    |
| 156    | 7    | 26.1  |
| 157    | 7    | 26.2  |
| 158    | 7    | 26.31 |
| 159    | 7    | 26.41 |
| 160    | 7    | 26.51 |
| 161    | 7    | 26.61 |
+--------+------+-------+
``````

I need both "cnt"-ranges but the higher count of 7s (from id 152 downwards) first. So i would have to split this result according to the gap in id and proceed with my calculations.

I googled a lot and also looked here on stackexchange, this is one partial solution i found so far

``````SELECT f.rowid, f.cnt, f.sweep FROM intersection f
WHERE cnt IN (SELECT MAX(cnt) FROM intersection)
AND NOT EXISTS (
SELECT * FROM intersection s
WHERE s.cnt = f.cnt
AND s.rowid = f.rowid+1)--or -1
``````

It returns, depending on +1 or -1, the lower or upper boundary of id but with that i can't compute the average of sweep for the given range.

Finally i need a query that looks for the highest "cnt"-value, the highest count of one range of (in this case 7s) split by the gap between the id's of these two groups.

I have no idea how to get there since i'm not that experienced but willing to learn a lot, so a little advice would be greatly appreciated!

Wanted output:

``````+-------+-----------+
| count | avg_sweep |
| 8     | 21.42     |
| 10    | 26.15     |
+-------+-----------+
``````
• What is the wanted output? Commented Mar 4, 2018 at 22:04
• I added a table. You can edit your question, too. Commented Mar 4, 2018 at 22:45
• thanks, too puzzled today to see that tiny link ;) Commented Mar 4, 2018 at 22:58

For each lower boundary, search the matching upper boundary:

``````WITH uppers(rowid) AS (
SELECT f.rowid FROM intersection f
WHERE cnt = (SELECT MAX(cnt) FROM intersection)
AND NOT EXISTS (
SELECT * FROM intersection s
WHERE s.cnt = f.cnt
AND s.rowid = f.rowid+1)
)
SELECT f.rowid AS lb,
(SELECT u.rowid FROM uppers u
WHERE u.rowid >= f.rowid
ORDER BY u.rowid ASC LIMIT 1
) ub
FROM intersection f
WHERE cnt = (SELECT MAX(cnt) FROM intersection)
AND NOT EXISTS (
SELECT * FROM intersection s
WHERE s.cnt = f.cnt
AND s.rowid = f.rowid-1);
``````
```lb  ub
--- ---
106 113
152 161
```

These values can then be used for BETWEEN:

``````WITH uppers(rowid) AS (
SELECT f.rowid FROM intersection f
WHERE cnt = (SELECT MAX(cnt) FROM intersection)
AND NOT EXISTS (
SELECT * FROM intersection s
WHERE s.cnt = f.cnt
AND s.rowid = f.rowid+1)
),
bounds(lb, ub) AS (
SELECT f.rowid,
(SELECT u.rowid FROM uppers u
WHERE u.rowid >= f.rowid
ORDER BY u.rowid ASC LIMIT 1)
FROM intersection f
WHERE cnt = (SELECT MAX(cnt) FROM intersection)
AND NOT EXISTS (
SELECT * FROM intersection s
WHERE s.cnt = f.cnt
AND s.rowid = f.rowid-1)
)
SELECT (SELECT count(*) FROM intersection
WHERE rowid BETWEEN bounds.lb AND bounds.ub
) AS count,
(SELECT avg(sweep) FROM intersection
WHERE rowid BETWEEN bounds.lb AND bounds.ub
) AS avg_sweep
FROM bounds;
``````
• very nice! thanks a lot, this solved my problem! could you maybe explain why i would have to use with? is it something like a placeholder or temporary variable? what was your plan to solve this special kind of problem, how did you slice the single steps? And a new problem arose, i would have to select the highest count and delete it. How would i achieve this? Commented Mar 5, 2018 at 13:37
• WITH is the same as a view or a subquery. In this case, `upper` is used only once, so you could just as well insert it there as a subquery. To ask a question, use the "Ask Question" button.
– CL.
Commented Mar 5, 2018 at 15:36