Select maximum id in each series-CodePudding

I need to select min and max ID in each number series "c_protokolu" in table similar to this. There are deletes from the table, so some IDs are missing.

id	c_protokolu	date
1	0	2021-06-22
2	1	2021-08-14
3	2	2022-01-06
4	0	2022-07-12
8	1	2022-09-09

I have come up if that, which works, but isn't very readable.

SELECT 
    id,
    IFNULL((SELECT 
                    (SELECT 
                                MAX(id)
                            FROM
                                vody v3
                            WHERE
                                v3.id < v2.id
                            LIMIT 1)
                FROM
                    vody v2
                WHERE
                    v2.id > v1.id AND c_protokolu = 0
                ORDER BY id ASC
                LIMIT 1),
            (SELECT 
                    MAX(id)
                FROM
                    vody v4)) AS max_id
FROM
    vody v1
WHERE
    c_protokolu = 0

Hopefully there is a better, simpler way to do this.

CodePudding user response：

looks only marginally better.

WITH CTE as 
(SELECT MAX(id) max_,MIN(id) min_ FROm vody WHERE c_protokolu = 0)
SELECT min_ as id,(SELECT id FROM vody WHERE id < max_ ORDER BY id DESC LIMIT 1) max_id FROM CTE
UNION
SELECT max_,(SELECT MAX(id) FROM vody) FROM CTE

id | max_id
-: | -----:
 1 |      3
 4 |      8

db<>fiddle here

SELECT min_ as id,(SELECT id FROM vody WHERE id < max_ ORDER BY id DESC LIMIT 1) max_id FROM (SELECT
MIN(id) min_,MAX(id) max_  FROm vody WHERE c_protokolu = 0) CTE
UNION
SELECT max_,(SELECT MAX(id) FROM vody) FROM (SELECT
MAX(id) max_ FROm vody WHERE c_protokolu = 0) CTE

id | max_id
-: | -----:
 1 |      3
 4 |      8

db<>fiddle here