I have a table with three columns: id, sort, name in PostgreSQL 9.5 database.
I need to select all rows starting from sort=1 and before the first break (ordered by sort).
For example I have:
id | sort | name
1 | 1 | 'ss'
2 | 2 | 'ss'
3 | 3 | 'ss'
4 | 4 | 'ss'
5 | 7 | 'ss'
6 | 8 | 'ss'
I want to select rows with id=1,2,3,4.
As you can see, there is a break in the sort sequence (4-7). So I only need the rows before this break.
How can I do this?
This will work for the special case:
SELECT *
FROM your_table
WHERE sort < (SELECT MIN(t.sort)
FROM your_table AS t
LEFT JOIN your_table AS t2
ON t.sort = t2.sort+1
WHERE t2.sort IS NULL
AND t.sort > (SELECT MIN(sort) FROM your_table))
Window functions can help you there. Look for lead which will give you the next value by a given order. With them you can avoid self joins.
One solution would be:
-- Getting the next value
with ahead as (
select id, sort, lead(sort) over (order by sort) sort_lead, "name"
from test1
),
-- Find where we have the first gap
gap as (
select min(sort) sort_limit from ahead
where sort_lead - sort > 1
)
-- Select everything starting from 1 until the gap
select * from test1
where sort between 1 and (select sort_limit from gap)
order by sort;
Assuming sort to be unique, just look at the delta between the window function row_number() and sort:
SELECT *
FROM (SELECT *, row_number() OVER (ORDER BY sort) AS grp FROM tbl) sub
WHERE sort = grp;
sort gets out of sync at the first gap.
Detailed explanation:
