Database
Enthusiast

## Find the minimum alignment date query based on alignment number

Hi Frinds,

I need to find the minimum alignment date of alignment numbers.

 Alignment No Alignment_Date 30 20161007 31 20161008 32 20161009 40 20161008 41 20161009 42 20161010 56 20161101 57 20161102 58 20161103

If you see from the table above, alignment no 30,31,32 which are in sequence has the minimum alignment date as 20161007, so this alignment date would be picked.

The next alignment no starts from 40,41,42. So here the alignment date that should be picking is the minimum of these sequence which is  20161008.

Again next starts from 56,57,58. So the mimun alignment date in these sequence is 20161101.

Can you guys help with the query to achive this?

Regards

SJ

## Re: Find the minimum alignment date query based on alignment number

drop table Example;
create multiset volatile table Example
(
Alignment_No integer,
Alignment_Date date
)primary index( Alignment_No)
on commit preserve rows;

insert into Example(30, cast('20161007' as date format 'YYYYMMDD'));
insert into Example(31, cast('20161008' as date format 'YYYYMMDD'));
insert into Example(32, cast('20161009' as date format 'YYYYMMDD'));
insert into Example(40, cast('20161008' as date format 'YYYYMMDD'));
insert into Example(41, cast('20161009' as date format 'YYYYMMDD'));
insert into Example(42, cast('20161010' as date format 'YYYYMMDD'));
insert into Example(56, cast('20161101' as date format 'YYYYMMDD'));
insert into Example(57, cast('20161102' as date format 'YYYYMMDD'));
insert into Example(58, cast('20161103' as date format 'YYYYMMDD'));

select
Alignment_No,
min( Alignment_No ) over( order by Alignment_No desc
rows between 1 following and 1 following) as Alignment_No_Next,
Alignment_Date
from Example
qualify case when Alignment_No-1 = Alignment_No_Next then 0 else 1 end = 1

;