I asked my friend Chatty, can you use recursion in SQL? For example I have a table (ID, value) ordered by increasing ID. Can you guide me through using recursion to select all the rows of consecutive repeated values using and creating an SQLite database. Provide the small sample data.
Is there a better way to do this?
A More Modern Alternative (Window Functions)
In real-world SQL, this problem is usually solved more efficiently using window functions like:
LAG()
ROW_NUMBER()
SUM() OVER
But recursion is excellent for learning traversal logic and hierarchical thinking.
Recursion example using sqlite on termux
DROP TABLE IF EXISTS sample;
CREATE TABLE sample (
id INTEGER PRIMARY KEY,
value TEXT
);
INSERT INTO sample (id, value) VALUES
(1, 'A'),
(2, 'A'),
(3, 'A'),
(4, 'B'),
(5, 'B'),
(6, 'C'),
(7, 'A'),
(8, 'A'),
(9, 'D'),
(10, 'D'),
(11, 'D'),
(12, 'D');
WITH RECURSIVE grouped AS (
SELECT
id,
value,
1 AS group_num
FROM sample
WHERE id = (SELECT MIN(id) FROM sample)
UNION ALL
SELECT
s.id,
s.value,
CASE
WHEN s.value = g.value
THEN g.group_num
ELSE g.group_num + 1
END
FROM sample s
JOIN grouped g
ON s.id = g.id + 1
)
SELECT
value,
MIN(id) AS start_id,
MAX(id) AS end_id,
COUNT(*) AS consecutive_count
FROM grouped
GROUP BY group_num, value
HAVING COUNT(*) > 1;
Comments