CODEWITHSUNDEEP
sqloracle-database
Jonjilla
Jonjilla

Reputation: 463

return only sequentially numbered values from oracle

I have an sql query using the listagg function to create a comma separated list into one returned field which is working correctly. But I'd like to only include results where the sequentially ordered results have sequence numbers that are sequential. They must all start with 0, but there is no specific end sequence number. They sometimes skip a sequence number, so I want to remove those

so for example, I want to include values that look like this

0
0,1
0,1,2
0,1,2,3

but I want to skip results that look like

1
0,2
0,1,3
0,0,2

is there some SQL syntax that would filter values out like this?

Upvotes: 1

Views: 58

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can probably do this with a having clause:

select listagg(col, ',') . . .
from t
group by . . .
having min(col) = 0 and
       count(*) = count(distinct col) and
       max(col) = count(*) - 1

These conditions should ensure that rows with non-sequential values are filtered out.

Upvotes: 2

Related Questions