2

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!!!

Community
  • 1
  • 1
Perdue
  • 479
  • 9
  • 19

2 Answers2

1

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;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Hi Gordon, thanks so much for your help! Your solution did not work for me, and that may be because I'm on a legacy system. I just got a lot of errors when I tried to run your code. However, I was able to find a solution thanks to a part of your code, which I am super grateful for. I've posted my solution below. Thanks again!!! – Perdue Feb 25 '15 at 15:10
  • Could you let me know how the ids - row_number() over (order by ids) as grp works? I would just like to understand the mechanics behind it. Thanks! – Perdue Feb 25 '15 at 15:17
  • @Perdue . . . I think the syntax errors were just mistyping of `ids` in the outer `select`. – Gordon Linoff Feb 26 '15 at 00:58
  • The only possible caveat with using `ROW_NUMBER()` is if there are duplicate values in `ids` (I'm assuming there aren't such values since the OP didn't mention any). Then you would want `DENSE_RANK()` (which also works if there aren't any duplicates). – David Faber Feb 26 '15 at 04:48
  • @GordonLinoff Thanks! I tried your solution again, and it works fine now as well. It looks like there are two ways to skin this cat. :) I did throw in an order by grp statement so the results are sorted correctly. Thanks again! – Perdue Feb 26 '15 at 15:31
0

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
Perdue
  • 479
  • 9
  • 19