Concat Min-Max GROUP BY to between query

0
votes

Good evening all, I have a table which contains like below :

Category ID
Cat1 1
Cat2 2
Cat1 3
Cat1 4
Cat1 5
Cat2 6
Cat2 7

I want to obtain a result like below

Cat1 1 - 1
Cat2 2
Cat1 3 - 5 (and not 3 - 4 - 5 (I need to select the Min & Max of the list)
Cat2 6
Cat2 7

I have began with the query

SELECT Category, LISTAGG(ID, ' ') WITHIN GROUP (ORDER BY Category) AS desc FROM table GROUP BY Category;

As you see, the Cat1 can contains 2 ID (Min & Max) but it is not the case for Cat2.

Have you an idea about this issue?

I hope to be clear.

thanks.

Cyril

2
sqloraclegroup-by
Why would cat1 have multiple results (1-1 and 3-5)? - sgeddes
I'm not sure this is possible in a single query. Can you create a table function? That would make it easy. - GolezTrol
Thanks for your answer. Cat1 can have multiple results. Yes i can create a table function. - shackdown
I assume that you overlooked the output for Cat2 6-7. - Gordon Linoff
@GordonLinoff yes indeed, I forgot. - shackdown

2 Answers

2
votes

You can do this using row_numbers(). In fact, it is a difference between the id and a row numbers that identifies a group of sequential ids:

select category,
       (case when min(id) = max(id) then id
             else min(id) || '-' || max(id)
        end) as id_range
from (select t.*,
             (id - 
              row_number() over (partition by category order by id)
             ) as grp
      from table t
     ) t
group by category, grp;
1
votes

Try this :: 

select category
     ,min(id) || (case when min(id)=max(id) then 
                                ' '
                           else 
                             '-'||max(id) end) as "MIN-MAX-VALUE"
from temp 
group by category;