CODEWITHSUNDEEP
sqloracle-database
Perdue
Perdue

Reputation: 479

Need to find ranges in a list and individuals in Oracle SQL

So I have a bit of a coding challenge handed to me by my employer, and I was hoping that I may find some help in how to program this. I have a list of ID numbers that would be integers like this:

1
2
3
5
7
8
9
11
12
13
15

What I need to do is to take those numbers and create a list like this:

1-3
5
7-9
11-13
15

I have been researching this issue for the majority of the day. I did find this solution, which has helped me: Find missing int values

I took the code from there and I tweaked it to this:

SELECT CASE WHEN a.ids +1  = a.lead_no - 1 THEN TO_CHAR (a.ids)
                   ELSE  TO_CHAR (a.lag_no)  || '-' ||TO_CHAR (a.lead_no)  
          END as   Missing_track_no   
FROM   (SELECT ids
              ,LEAD (ids, 1, NULL) OVER (ORDER BY ids ASC) as lead_no
              ,lag (ids, 1, null) over (order by ids asc) as lag_no
        FROM   xxxxx_test) a  
WHERE   a.lead_no = a.ids + 1

What I end up getting in my output is this:

-2
1-3
5-8
7-9
9-12
11-13

This is probably the closest I've come all day to a solution. I am hoping that there is someone who can take a look at my code and let me know where I am going wrong or if SQL isn't capable of producing a list like the one I've described above and I need to go another direction.

Thanks!!!

Upvotes: 2

Views: 102

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

You have the data in xxxxx_test. That is a good start. You need to find sequences of adjacent numbers and then summarize them. My preferred solution is to use a difference between the number and row_number(). This is constant for numbers that are increasing by 1:

select (case when min(ids) < max(ids) then min(ids) || '-' || max(ids)
             else cast(min(ids) as varchar2(255))
        end)
from (select t.*, ids - row_number() over (order by ids) as grp
      from xxxxx_test t
     ) t
group by grp;

Upvotes: 1

Perdue
Perdue

Reputation: 479

This is the solution that I came up with that worked for me.

select case when b.min_id = b.max_id
             then cast(b.min_id as varchar2(255))
             else b.min_id || '-' || b.max_id
       end as range
from (select min(a.ids) as min_id
            ,max(a.ids) as max_id
            ,a.grp
      from (select t.ids
           ,ids - row_number() over (order by ids) as grp
            from xxxxx_test t) a
      group by a.grp
      order by a.grp) b

Upvotes: 0

Related Questions