René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Consecutive Range with SQL

set feedback off

create table t_ (specimen_id varchar2(5), reader char(3));

insert into t_ values ('001', 'JWC');
insert into t_ values ('002', 'JWC');
insert into t_ values ('003', 'JWC');
insert into t_ values ('004', 'JWC');
insert into t_ values ('005', 'JWC');
insert into t_ values ('006', 'JWC');
insert into t_ values ('007', 'JWC');
insert into t_ values ('008', 'JWC');
insert into t_ values ('009', 'JWC');
insert into t_ values ('010', 'JWC');
insert into t_ values ('011', 'JWC');
insert into t_ values ('012', 'JWC');
insert into t_ values ('013', 'JWC');
insert into t_ values ('014', 'JWC');
insert into t_ values ('015', 'JWC');
insert into t_ values ('016', 'JWC');
insert into t_ values ('017', 'JWC');
insert into t_ values ('018', 'JWC');
insert into t_ values ('019', 'JWC');
insert into t_ values ('020', 'JWC');
insert into t_ values ('021', 'JWC');
insert into t_ values ('022', 'JWC');
insert into t_ values ('023', 'JWC');
insert into t_ values ('024', 'JWC');
insert into t_ values ('025', 'JWC');
insert into t_ values ('026', 'JWC');
insert into t_ values ('027', 'JWC');
insert into t_ values ('028', 'JWC');
insert into t_ values ('029', 'JWC');
insert into t_ values ('030', 'JWC');
insert into t_ values ('031', 'JWC');
insert into t_ values ('032', 'JWC');
insert into t_ values ('033', 'JWC');
insert into t_ values ('034', 'JWC');
insert into t_ values ('035', 'JWC');
insert into t_ values ('036', 'JWC');
insert into t_ values ('037', 'JWC');
insert into t_ values ('038', 'JWC');
insert into t_ values ('039', 'JWC');
insert into t_ values ('040', 'JWC');
insert into t_ values ('041', 'JWC');
insert into t_ values ('042', 'JWC');
insert into t_ values ('043', 'JWC');
insert into t_ values ('044', 'JWC');
insert into t_ values ('045', 'JWC');
insert into t_ values ('046', 'JWC');
insert into t_ values ('047', 'JWC');
insert into t_ values ('048', 'JWC');
insert into t_ values ('049', 'JWC');
insert into t_ values ('050', 'JWC');
insert into t_ values ('051', 'JWC');
insert into t_ values ('052', 'JWC');
insert into t_ values ('053', 'JWC');
insert into t_ values ('054', 'JWC');
insert into t_ values ('055', 'JWC');
insert into t_ values ('056', 'DSO');
insert into t_ values ('057', 'DSO');
insert into t_ values ('058', 'DSO');
insert into t_ values ('059', 'DSO');
insert into t_ values ('060', 'DSO');
insert into t_ values ('061', 'DSO');
insert into t_ values ('062', 'DSO');
insert into t_ values ('063', 'DSO');
insert into t_ values ('064', 'DSO');
insert into t_ values ('065', 'DSO');
insert into t_ values ('066', 'DSO');
insert into t_ values ('067', 'DSO');
insert into t_ values ('068', 'DSO');
insert into t_ values ('069', 'DSO');
insert into t_ values ('070', 'DSO');
insert into t_ values ('071', 'DSO');
insert into t_ values ('072', 'DSO');
insert into t_ values ('073', 'DSO');
insert into t_ values ('074', 'DSO');
insert into t_ values ('075', 'DSO');
insert into t_ values ('076', 'DSO');
insert into t_ values ('077', 'DSO');
insert into t_ values ('078', 'DSO');
insert into t_ values ('079', 'DSO');
insert into t_ values ('080', 'RSL');
insert into t_ values ('081', 'RSL');
insert into t_ values ('082', 'RSL');
insert into t_ values ('083', 'RSL');
insert into t_ values ('084', 'RSL');
insert into t_ values ('085', 'RSL');
insert into t_ values ('086', 'RSL');
insert into t_ values ('087', 'RSL');
insert into t_ values ('088', 'RSL');
insert into t_ values ('089', 'RSL');
insert into t_ values ('090', 'RSL');
insert into t_ values ('091', 'RSL');
insert into t_ values ('092', 'RSL');
insert into t_ values ('093', 'RSL');
insert into t_ values ('094', 'RSL');
insert into t_ values ('095', 'RSL');
insert into t_ values ('096', 'RSL');
insert into t_ values ('097', 'DSO');
insert into t_ values ('098', 'DSO');
insert into t_ values ('099', 'DSO');
insert into t_ values ('100', 'DSO');
insert into t_ values ('101', 'JWC');
insert into t_ values ('102', 'JWC');
insert into t_ values ('103', 'JWC');
insert into t_ values ('104', 'JWC');
insert into t_ values ('105', 'JWC');
insert into t_ values ('106', 'JWC');
insert into t_ values ('107', 'JWC');
insert into t_ values ('108', 'JWC');
insert into t_ values ('109', 'JWC');
insert into t_ values ('110', 'JWC');
insert into t_ values ('111', 'JWC');
insert into t_ values ('112', 'JWC');
insert into t_ values ('113', 'JWC');
insert into t_ values ('114', 'JWC');
insert into t_ values ('115', 'JWC');
insert into t_ values ('116', 'JWC');
insert into t_ values ('117', 'JWC');
insert into t_ values ('118', 'JWC');
insert into t_ values ('119', 'JWC');
insert into t_ values ('120', 'JWC');
insert into t_ values ('121', 'JWC');
insert into t_ values ('122', 'JWC');
insert into t_ values ('123', 'JWC');
insert into t_ values ('124', 'JWC');
insert into t_ values ('125', 'JWC');
insert into t_ values ('126', 'JWC');
insert into t_ values ('127', 'JWC');
insert into t_ values ('128', 'JWC');
insert into t_ values ('129', 'JWC');
insert into t_ values ('130', 'JWC');
insert into t_ values ('131', 'JWC');
insert into t_ values ('132', 'JWC');
insert into t_ values ('133', 'JWC');
insert into t_ values ('134', 'JWC');
insert into t_ values ('135', 'JWC');
insert into t_ values ('136', 'JWC');
insert into t_ values ('137', 'JWC');
insert into t_ values ('138', 'JWC');
insert into t_ values ('139', 'JWC');
insert into t_ values ('140', 'JWC');
insert into t_ values ('141', 'JWC');
insert into t_ values ('142', 'JWC');
insert into t_ values ('143', 'JWC');
insert into t_ values ('144', 'JWC');
insert into t_ values ('145', 'JWC');
insert into t_ values ('146', 'JWC');
insert into t_ values ('147', 'JWC');
insert into t_ values ('148', 'JWC');
insert into t_ values ('149', 'JWC');
insert into t_ values ('150', 'JWC');
insert into t_ values ('151', 'JWC');
insert into t_ values ('152', 'JWC');
insert into t_ values ('153', 'JWC');
insert into t_ values ('154', 'JWC');
insert into t_ values ('155', 'JWC');
insert into t_ values ('156', 'JWC');
insert into t_ values ('157', 'JWC');
insert into t_ values ('158', 'JWC');
insert into t_ values ('159', 'JWC');
insert into t_ values ('160', 'JWC');
insert into t_ values ('161', 'JWC');
insert into t_ values ('162', 'JWC');
insert into t_ values ('163', 'JWC');
insert into t_ values ('164', 'JWC');
insert into t_ values ('165', 'JWC');
insert into t_ values ('166', 'JWC');
insert into t_ values ('167', 'JWC');
insert into t_ values ('168', 'JWC');
insert into t_ values ('169', 'JWC');
insert into t_ values ('170', 'JWC');
insert into t_ values ('171', 'JWC');
insert into t_ values ('172', 'JWC');
insert into t_ values ('173', 'JWC');
insert into t_ values ('174', 'JWC');
insert into t_ values ('175', 'JWC');
insert into t_ values ('176', 'JWC');
insert into t_ values ('177', 'JWC');
insert into t_ values ('178', 'JWC');
insert into t_ values ('179', 'JWC');
insert into t_ values ('180', 'JWC');
insert into t_ values ('181', 'JWC');
insert into t_ values ('182', 'JWC');
insert into t_ values ('183', 'JWC');
insert into t_ values ('184', 'JWC');
insert into t_ values ('185', 'JWC');
insert into t_ values ('186', 'JWC');
insert into t_ values ('187', 'JWC');
insert into t_ values ('188', 'JWC');
insert into t_ values ('189', 'JWC');
insert into t_ values ('190', 'JWC');
insert into t_ values ('191', 'JWC');
insert into t_ values ('192', 'JWC');
insert into t_ values ('193', 'JWC');
insert into t_ values ('194', 'JWC');
insert into t_ values ('195', 'JWC');
insert into t_ values ('196', 'JWC');
insert into t_ values ('197', 'JWC');
insert into t_ values ('198', 'JWC');
insert into t_ values ('199', 'JWC');
insert into t_ values ('200', 'JWC');
insert into t_ values ('201', 'JWC');
insert into t_ values ('202', 'JWC');

select start_, end_, reader from (
  select 
    first_value(val) over (partition by diff) start_,
    last_value(val)  over (partition by diff) end_,
    reader,
    row_number()     over (partition by diff order by val) r
  from (
    select 
      row_number() over(partition by reader order by to_number(specimen_id)) -
      to_number(specimen_id) diff,
      to_number(specimen_id) val,
      reader 
    from
      t_
  )
) where r=1
order by start_;

drop table t_;