In SQLite, how to group ranges of values and sort the groups

Question

In SQLite, I have a table with 3 columns: id, value (integer), and sort_me. What I'm trying to query is groups of rows whose values are within +-50 range, and sort everything, within each group and across groups, by sort_me ascending. If a group consists of just one member, discard it.

Here is an example table:

id, value, sort_me
 1,     1,      15
 2,   102,      22
 3,     3,       7
 4,   101,      13
 5,   312,      53
 6,     6,      34
 7,   309,       3
 8,   104,       9
 9,   521,       8

The expected result should be

7,5
3,1,6
8,4,2

So far I come up with

SELECT concat_ws(',', v1.id, group_concat(v2.id ORDER BY v2.sort_me ASC))
    FROM t v1, t v2
    WHERE v1.id < v2.id AND abs(v1.value - v2.value) < 50
    GROUP BY v1.id
    ORDER BY v1.sort_me ASC

Which yields

3,6
4,8
1,3,6
2,8,4
5,7

This is obviously wrong in multiple ways. First, beside the full groups, some sub-groups are also there. Second, the sorting inside group_concat() only affects v2 and v1 is always appears first. Third, the order across groups are also wrong because v1.sort_me may not be the smallest among each group.

Answer 1

I came up with a query myself. Not sure if a simpler solution exists.

SELECT DISTINCT *
FROM (
    SELECT group_concat(v2_id ORDER BY v2_sort_me ASC)
    FROM (
        SELECT v1.id AS v1_id, v2.id AS v2_id, v1.sort_me AS v1_sort_me, v2.sort_me AS v2_sort_me
        FROM t v1
        JOIN t v2
        ON abs(v1.value - v2.value) < 20
    )
    GROUP BY v1_id HAVING COUNT(*) > 1
    ORDER BY v1_sort_me ASC
)

Note: The ORDER BY clause inside group_concat() is only supported since SQLite 3.44.0.

Answer 2

To be honest, I'm not entirely clear on your question. If I understand correctly, you're looking to categorize values into intervals of 50, such as 1-50, 51-100, 101-150, and so on. After that, you want to group the IDs based on these intervals and sort them according to the "sort_me" column. Beside concatenating those ids, you want to count the number of ids in each group and discard those with 1 or less. If this is what you are looking for then you may try the following query,

WITH Intervals AS (
SELECT 
    id,
    CASE 
        WHEN value BETWEEN 1 AND 50 THEN '1-50'
        WHEN value BETWEEN 51 AND 100 THEN '51-100'
        WHEN value BETWEEN 101 AND 150 THEN '101-150'
        -- Add more intervals as needed
        ELSE 'Unknown'
    END AS value_interval,
    sort_me
FROM 
    your_table
)
SELECT 
    value_interval,
    GROUP_CONCAT(id ORDER BY sort_me) AS grouped_ids,
    COUNT(*) AS num_ids
FROM 
    Intervals
GROUP BY 
    value_interval
HAVING 
    num_ids > 1
ORDER BY 
    value_interval;

There is no straight answer for your query as far as I know. Well as I said, I might be interpreting your question wrong.