hi all
i have a table with two cols A and B
and following values..


A B
1 10000
1 10001
1 10002
1 10006
1 10007
2 10008
2 10009
2 10010
2 10011
2 10012
2 10015
3 10021
3 10022
3 10023
3 10031
3 10032
3 10033
3 10034
3 10035
4 10041
4 10042
4 10044

i want to get following output

A B C
1 10000 10002
1 10006 10007
2 10008 10015
3 10021 10023
3 10031 10035
4 10041 10042
4 10044 10044

could anyone help me ?

eokumus79@gmail.com wrote:

> A B
> 1 10000
> 1 10001
> 1 10002
> 1 10006
> 1 10007
> 2 10008
> 2 10009
> 2 10010
> 2 10011
> 2 10012
> 2 10015
> 3 10021
> 3 10022
> 3 10023
> 3 10031
> 3 10032
> 3 10033
> 3 10034
> 3 10035
> 4 10041
> 4 10042
> 4 10044
>
> i want to get following output
>
> A B C
> 1 10000 10002
> 1 10006 10007
> 2 10008 10015
> 3 10021 10023
> 3 10031 10035
> 4 10041 10042
> 4 10044 10044

select
a,
min(b),
max(b)
from
(
select
a - rank() over (partition by A order by b asc) as grp,
a
from mytab
) dt
group by a, grp
order by 1,2;

If a/b is not unique, replace rank with dense_rank...

Dieter

Dieter Noeth wrote:

> select
> a,
> min(b),
> max(b)
> from
> (
> select
> a - rank() over (partition by A order by b asc) as grp,
> a
> from mytab
> ) dt
> group by a, grp
> order by 1,2;

Sorry, of course it's:

select
a,
min(b),
max(b)
from
(
select
b - rank() over (partition by A order by b asc) grp,
a,
b
from mytab
) dt
group by a, grp
order by 1,2;

Dieter

thank you very much. you are great

Dieter Noeth wrote:
> Dieter Noeth wrote:
>
> > select
> > a,
> > min(b),
> > max(b)
> > from
> > (
> > select
> > a - rank() over (partition by A order by b asc) as grp,
> > a
> > from mytab
> > ) dt
> > group by a, grp
> > order by 1,2;
>
> Sorry, of course it's:
>
> select
> a,
> min(b),
> max(b)
> from
> (
> select
> b - rank() over (partition by A order by b asc) grp,
> a,
> b
> from mytab
> ) dt
> group by a, grp
> order by 1,2;
>
> Dieter